Concatenate in by statement

[abak]

Hi,

I want to concatenate the results from one column from a data set into a single row using a by statement. The data looks something like this:

doc  operation

12    111

12    112

12    119

14    823

14    1010

And my code looks like this:

data final;
   set final;
   by doc;
   retain steps;
   if first.doc and last.doc then do; /*If there is only one row for the document, then*/
          steps = strip(put(operation, .15)); /*The idea is to turn it into a character and strip out extra spaces*/
          output; /*now output the results for this document*/
          end; /*end do loop*/
    else then do; /*If it's not just one row per document...*/
            if first.doc then Steps = strip(put(operation, 15.)); /*initiate steps value with first operation*/
           else steps = strip(steps) || ';' || strip(operation); /*for all other steps, concatenate with a ; between values*/
           if last.doc then output; /* If it's the last doc, then output the results from steps */
           end; /*end loop*/
run;

The results, however, are an empty data set.

[Urban_Science]

I don't have a way to test anything right now, but there are two things that I would do to solve this.

1. Add  "length step $ 1000;"  (or a similarity large number that will be of enough length to hold the final list) 

2. replace all "OUTPUT;" with "OUTPUT_IND = 1;" so that I could see the values in the data vector at each row.  You can also include "put _ALL_;" at the end which will output first.doc and last.doc

[abak]

I added 1. Good idea. Thank you!

I don't understand 2 but I tried adding OUTPUT_IND = 1. The result was that it didn't even output the new data set.

[Urban_Science]

2 is my way of troubleshooting code by removing output statements in the code.  For data steps, if there is an output somewhere in the data step, then SAS will only output the row when it hits that output statement; however, if there are on output statements in the code, then SAS will output every row when it gets to the "run;" statement.

[abak]

Got it. When I don't do that it outputs nothing. Not even an empty data set. Just nothing.

That's a good tip though that I'll remember to try again at another time when I run into complications. Thanks!

[Urban_Science]

Does the log show that you read in rows from a dataset?  Something like 

NOTE: There were 158885 observations read from the data set WORK.FINAL

[novinosrin]

What is your expected result?

Please provide a complete comprehensive sample of input and expected output sample?

[abak]

I gave you an example of the input.

Output: 

doc  operation   steps

12    119            111;1112;119

14    1010          832;1010

[novinosrin · Accepted solution]

data have;
input doc  operation;
cards;
12    111
12    112
12    119
14    823
14    1010
;

data want;
set have;
by doc;
length op $50;
retain op;
if first.doc then op=put(operation,8.);
else op=catx(';',op,operation);
if last.doc;
run;

[abak]

Thank you for that answer.

Does anyone understand why mine failed though?

[novinosrin]

The reason yours failed is incorrect use of by group processing in a sas datastep. Just a matter of understanding this concept and you'll do fine. All the best!