topic Re: Concatenate in by statement in SAS Programming

Concatenate in by statement

abak — Fri, 17 Aug 2018 17:17:08 GMT

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.

Re: Concatenate in by statement

Urban_Science — Fri, 17 Aug 2018 17:31:10 GMT

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

Re: Concatenate in by statement

novinosrin — Fri, 17 Aug 2018 17:33:45 GMT

What is your expected result?

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

Re: Concatenate in by statement

abak — Fri, 17 Aug 2018 17:44:39 GMT

I gave you an example of the input.

Output:

doc operation steps

12 119 111;1112;119

14 1010 832;1010

Re: Concatenate in by statement

abak — Fri, 17 Aug 2018 17:49:16 GMT

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.

Re: Concatenate in by statement

Urban_Science — Fri, 17 Aug 2018 17:56:05 GMT

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.

Re: Concatenate in by statement

abak — Fri, 17 Aug 2018 18:00:16 GMT

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!

Re: Concatenate in by statement

Urban_Science — Fri, 17 Aug 2018 18:02:51 GMT

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

Re: Concatenate in by statement

novinosrin — Fri, 17 Aug 2018 18:03:51 GMT

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;

Re: Concatenate in by statement

abak — Fri, 17 Aug 2018 18:49:10 GMT

Thank you for that answer.

Does anyone understand why mine failed though?

Re: Concatenate in by statement

novinosrin — Fri, 17 Aug 2018 18:57:10 GMT

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!