I am trying take muliple records and summarize them by displaying on a single line BY ProgramName.

The loop needs to identify the number of states within the ProgramName display all the states within that ProgramName on one line like this.

ProgramName State
JEP Braveheart VA,MD,PA
Holiday Mailer NY,NJ, DE

The data would normally display like this.
ProgramName State
JEP Braveheart VA
JEP Braveheart MD
JEP Braveheart PA

The below code is what I tried but it doesn't know when to stop and restart when there is a new program. It is a pitiful example but a least I tried. I am ready to throw it away and start from scratch.

-SET &COUNT = 1;
-SET &ECHO=ALL;
-* Print all unique values and place them
-* into a HOLD file which will be used for reading.
TABLE FILE V_USARPTMRRMAILINGINFO
SUM
SiteState
ProgramName
BY ProgramName NOPRINT
BY SiteState NOPRINT
ON TABLE SAVE AS SITESTAT FORMAT ALPHA
END
-RUN
-* &LINES stores the number of records returned from
-* the previous TABLE request. This is the number of
-* unique values and is the maximum number of
-* times we will loop through the HOLD file to create
-* variables for reporting purposes.
-REPEAT READLOOP WHILE &COUNT LE &LINES;
-READ SITESTAT &REC.&COUNT.A8.
-SET &COUNT = &COUNT + 1;
-READLOOP
-* Reset &COUNT to 1 to begin looping through the newly
-* created variables. Set a variable equal to the
-* previous number of records returned since &LINES will
-* be reset when we attempt a new TABLE request.
-SET &COUNT = 1;
-SET &L = &LINES;
-* First value is compared with &REC1. Then the value
-* of &COUNT is incremented. Once in the loop, we add
-* OR &REC2, OR &REC3, OR &REC4, and so on, until the
-* counter is greater than the number of records. Then
-* we have all the values in the WHERE clause and the
-* report is printed.
-*-SET &STATE=EDIT('&REC.&COUNT', '$9999999');
-*-SET &REC.&COUNT=EDIT('&REC.&COUNT', '$9999999');
DEFINE FILE V_USARPTMRRMAILINGINFO
STATES/A40=IF EDIT('&REC2','$$$$$$99') NE '' AND EDIT('&REC3','$$$$$$99') EQ '' THEN EDIT('&REC1','$$$$$$99') | ',' | EDIT('&REC2','$$$$$$99') ELSE
IF EDIT('&REC2','$$$$$$99') NE '' AND EDIT('&REC3','$$$$$$99') NE '' THEN EDIT('&REC1','$$$$$$99') | ',' | EDIT('&REC2','$$$$$$99') |','| EDIT('&REC3','$$$$$$99') ELSE '&REC1';
END
TABLE FILE V_USARPTMRRMAILINGINFO
PRINT PROGRAMNAME
BY STATES
WHERE RECORDLIMIT EQ 1000
END

This message has been edited. Last edited by: Kerry,

You shouldn't need to do any looping.

TABLE FILE V_USARPTMRRMAILINGINFO
SUM
CNT.DST.State
BY ProgramName
SUM
State
BY ProgramName
BY State
END

Notes:

CNT = Count
DST = Distinct (in case the same state is there for the same ProgramName more than once. If that cannot happen, remove the DST.)

This is a multi verb request - the first verb SUM counts the number of States per ProgramName, the second verb SUM displays all the unique States by ProgramName.

I hope this provides a new beginning.

I just realized you want the States on one line.

Change

BY State

to

Across State

. Unfortunately, that will not give you exactly what you want, but start with the code I've provided and I'll get back to you!

Scrap all of that for a few minutes!

Thanks for the quick response. I did what you said and you are right I am not quite there yet but I have already learned something. I did get an error though when trying to use the CNT.DST.State

(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION

No matter I have a field in which to sum without doing a count or dst.

I will wait patiently for your response for me to get them on one line. VA, MD, PA etc.

There's got to be a better way! I'm sure other people have had to do this (not me though!).

Here's some new code:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

TABLE FILE CAR
SUM
COUNTRY NOPRINT
BY BODYTYPE
BY COUNTRY
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
COUNT COUNTRY AS 'COUNTRY_COUNT'
BY BODYTYPE
SUM
COUNTRY
BY BODYTYPE
ACROSS COUNTRY NOPRINT
ON TABLE HOLD AS HOLD2
END

DEFINE FILE HOLD2
COUNTRY_LIST/A300 =  E03 || (E04) || (E05) || (E06) || (E07);
END

TABLE FILE HOLD2
PRINT
COUNTRY_COUNT
COUNTRY_LIST
BY BODYTYPE
END

Substitute your fieldnames for the example ones I used.
This unfortunately assumes you know how many COUNTRYs there are (the E02, E03... field aliases are used because when you do an ACROSS and HOLD, the fieldname is messed up).

I hope someone else tightens this up a bit.

Slight revision!

The HOLDFORMAT ALPHA is important.

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA

TABLE FILE CAR
SUM
COUNTRY NOPRINT
BY BODYTYPE
BY COUNTRY
ON TABLE HOLD AS HOLD1
END

? HOLD HOLD1

TABLE FILE HOLD1
COUNT COUNTRY AS 'COUNTRY_COUNT'
BY BODYTYPE
SUM
COUNTRY
BY BODYTYPE
ACROSS COUNTRY NOPRINT
ON TABLE HOLD AS HOLD2
END

DEFINE FILE HOLD2
COUNTRY_LIST/A300 =  E03 || (' ' | E04) || (' ' | E05) || (' ' | E06) || (' ' | E07);
END

TABLE FILE HOLD2
PRINT
COUNTRY_COUNT
COUNTRY_LIST
BY BODYTYPE
END

Take a look at this and see if it helps. I used the CAR file to build a sample of your file.
-*----------------------------------------------------
DEFINE FILE CAR
CNT/I5 WITH CAR = CNT + 1;
PRGM_NM/A15 WITH CAR = DECODE CNT (
1 'JEP BRAVEHEART'
2 'JEP BRAVEHEART'
3 'JEP BRAVEHEART'
4 'XYZ ZZZZZZZZZZ'
5 'XYZ ZZZZZZZZZZ'
6 'XYZ ZZZZZZZZZZ'
7 'XYZ ZZZZZZZZZZ'
);
STATE/A2 WITH CAR = DECODE CNT (
1 'VA'
2 'MD'
3 'PA'
4 'NE'
5 'IA'
6 'CO'
7 'KS'
);
END
-*
TABLE FILE CAR
PRINT PRGM_NM STATE
WHERE RECORDLIMIT EQ 7
ON TABLE HOLD AS YOURFILE FORMAT ALPHA
END
-*
TABLE FILE YOURFILE
SUM
COMPUTE CNTR/I3 = IF PRGM_NM EQ LAST PRGM_NM THEN CNTR + 1 ELSE 1;
BY PRGM_NM
BY STATE
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
STATE_LIT/A149 = IF PRGM_NM EQ LAST PRGM_NM
THEN SUBSTR(149, STATE_LIT, 1, 146, 146, 'A146') || ',' || STATE
ELSE STATE;
END
-*
TABLE FILE HOLD
SUM STATE_LIT
BY PRGM_NM
END
-*---------------------------------------------------

Jim

Jim, I don't have a predefined list of ProgramNames or States Associated with those Program Names. I would have to be able to dynamically determine when a new program started and get all those states within the program and display them on one line.

This code gets me 80% there (thanks Francis)
TABLE FILE V_USARPTMRRMAILINGINFO
SUM
DropDate AS 'Drop Date'
ProgramName AS 'Market Code'
TotalLettersMailed AS 'Site Mailed'
BY ProgramName NOPRINT
SUM
State
BY ProgramName
BY State NOPRINT

But the states (that are not pre-defined) need to display on one line instead off down the column.

Market Code State
01/19/2007 JEP Lake IL Add Antenna ??,??,??

Hopefully the definitive solution!

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

TABLE FILE CAR
SUM
CNT.DST.COUNTRY AS 'COUNTRY_COUNT'
BY BODYTYPE
BY COUNTRY
ON TABLE HOLD AS HOLD1
END

DEFINE FILE HOLD1
COUNTRY_LIST/A300 =  IF BODYTYPE EQ LAST BODYTYPE THEN SUBSTR(300, COUNTRY_LIST, 1, 250, 250, 'A250') || (' ' | COUNTRY) ELSE COUNTRY;
END

TABLE FILE HOLD1
SUM
COUNTRY_COUNT
COUNTRY_LIST
BY BODYTYPE
END

The CNT.DST.COUNTRY in the first TABLE FILE results in 1 for each row.

The SUBSTR (substring) function is used to ensure you can append to the existing list of Countries.

Francis You're the Man!!! I appreciate everyone's help!!! But Francis you hit it on the nail it worked perfectly. You have open many opportunities to solve problems with this one. I am sure it helped you as well, but I did want to say on thing. When I update the procedure I got back 00 in the row for my state_list. Was very confused but this is a bug in 7.1.4 it worked for the CAR file but in order for it to work for my procedure I had to remove the V in the master file. This has fixed a number of problem for me so I assumed it was again the issue. Thanks again. I hope to run into you again some time on line.

Hi Clay,

Just curious, does this work for you?

TABLE FILE V_USARPTMRRMAILINGINFO
SUM
COMPUTE CNTR/I3 = IF ProgramName EQ LAST ProgramName THEN CNTR + 1 ELSE 1;
BY ProgramName
BY SiteState
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
STATE_LIT/A149 = IF ProgramName EQ LAST ProgramName
THEN SUBSTR(149, STATE_LIT, 1, 146, 146, 'A146') || ',' || SiteState
ELSE SiteState;
END
-*
TABLE FILE HOLD
SUM STATE_LIT
BY ProgramName
END

Jim

Jim, I guess Great Minds Think a like. You and Francis hit it on the mark!!! Yours is slightly different and I guess depending on the solution both can be used in different cases especially because cnt.dst can only be used with SUM. Thanks for taking the time to work on this. I am humbled and most grateful.

Clay,

Glad you're happy!

Francis,

Is there size limitation to using the SUBSTR function? I used the same concept as the state for some other larger field sizes. And it seems like when the string gets too large it starts to chop of names and repeats only pieces of the information. I am currently using this for the name field: SDO_LIST/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIST, 1, 250, 450, 'A450')

(removed names)

The length for the one with the problem is about 300 characters. I've even tried to increase the string end value but to no avail. Can you provide any guidance on this problem.

This message has been edited. Last edited by: claymeone,

Try this code

  
TABLE FILE V_USARPTMRRMAILINGINFO
SUM
ProgramName NOPRINT
SiteState NOPRINT
AND COMPUTE States/A400 = IF ProgramName EQ LAST ProgramName THEN
SUBSTR(400, LAST States, 1, 396, 396, 'A396') || (', ' | SiteState) ELSE SiteState;
BY ProgramName
BY SiteState NOPRINT
ON TABLE HOLD AS HLD1
END

TABLE FILE HLD1
SUM States
BY ProgramName
END

Texas, My hope that it is good and faithful servant Ok back to business. I tried your suggest. I had to increase the field size (400) to 800 or I received an error but it seemed to have gotten worse

(names removed) I couldn't tell you why. This is what I used.
SDO_LIT/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIT, 1, 396, 396, 'A396') || ('' | SDO) ELSE SDO;

This almost gets me there but not quite.
SDO_LIT/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIT, 1, 250, 450, 'A450') || ('' | SDO) ELSE SDO;

Any more suggestions?

This message has been edited. Last edited by: claymeone,

Clay,

What is the size of the SDO field?

Jim

Clay,

You coded your SUBSTR incorrectly. Try the following:

SUBSTR(800, SDO_LIT, 1, 450, 450, 'A450')

The parameters for the function are:

Input Length: 800
Input Field: SDO_LIT
Start Position: 1
End Position: 450
Total Length to Extract: 450
Output Format: A450

You were telling it to start in position 1 and to stop in position 250 but you were telling it to extract 450 characters.

Here's an example using the CAR file.

DEFINE FILE CAR
NEWCOUNTRY/A800=
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY |
COUNTRY | COUNTRY | COUNTRY | COUNTRY | COUNTRY;
SUBCOUNTRY/A450=SUBSTR(800, NEWCOUNTRY, 1, 450, 450, 'A450');
END
TABLE FILE CAR
PRINT NEWCOUNTRY SUBCOUNTRY
BY COUNTRY
END

Clay,

Off the topic: Who are you working for in Frederick, MD? I commute to Gaithersburg everyday from Frederick.

Just curious.

Clay,

Also, in the example I used above:

STATE_LIT/A149 = IF ProgramName EQ LAST ProgramName
THEN SUBSTR(149, STATE_LIT, 1, 146, 146, 'A146') || ',' || SiteState
ELSE SiteState;

I set the size of STATE_LIT to 149 because I figured there could be 50 SiteStates(size = 2) and one comma between each SiteState for a maximum size of (50 * 2) + (50 - 1) = 149.

The 146 that was used is 3(size of SiteState plus a comma) less than that total length of STATE_LIT(149).

So...We need to know the maximum number of SDO's that you think you will have, the size of each SDO and then what you want between each SDO.

Example: Up to 20 SDO's, length of SDO = 15, 1 comma between each SDO

This would need the following:

SDO_LIT/A319 = IF ProgramName EQ LAST ProgramName
THEN SUBSTR(319, SDO_LIT, 1, 303, 303, 'A303') || ',' || SDO
ELSE SDO;

(20 * 15) + (20 - 1) = 319
319 - (15 + 1) = 303

Let me know if this helps.

Jim

IF it is A800 then use 800 minus the length of SDO for example is SDO is 20 then


SDO_LIT/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIT, 1, 780, 780, 'A780') || ('' | SDO) ELSE SDO;

in the example you used state which was 2 characters long for each project.

Hope this works

Mickey, thanks for your participation in this issue. I agree with your logic but I've tried that one and it didn't work. I get the same think as the other: (removed names) It odd but maybe it's a bug? Or should I not be using SET HOLDFORMAT=ALPHA. I don't know. This is what I used to receive the above weird stuff: SDO_LIT/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIT, 1, 450, 450, 'A450') || ('' | SDO) ELSE SDO;

This message has been edited. Last edited by: claymeone,

Mickey, I live in Germantown, and work for a small company called Unison here in Frederick. It's a real estate company. Do you work for Lockeed Martin in Gaithersburg?

Texas, would I have to find out the length of the SDO name first? Or can I use the field legth of the SDO field size which is A127V.

Clay,

I work at NIST off Clopper RD.

The last thing I would check would be the length of you INPUT field SDO_LIT. Is SDO_LIT really 800 characters long? What is the ACTUAL and USAGE formats for this field in your MFD? If you PRINT SDO_LIT does it show up correctly?

If SDO_LIT is not 800 characters long, change the "800" in your SUBSTR statement to the proper length.

Clay,

One other thought:

If the ACTUAL and USAGE formats for SDO_LIT have a V on the end in the MFD, try removing the V from the format.

Make it A800 instead of A800V in the MFD.

you can use 127. The 800 need to be the maximum size of all the fields concat together. 127 * n

then adjust the number in the function accordingly

Hope this Helps

Clay,

Has this been resolved for you?