BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
abak
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

10 REPLIES 10
Urban_Science
Quartz | Level 8

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
Obsidian | Level 7
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
Quartz | Level 8

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
Obsidian | Level 7
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
Quartz | Level 8

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
Tourmaline | Level 20

What is your expected result?

 

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

abak
Obsidian | Level 7

I gave you an example of the input.

 

Output: 

 

doc  operation   steps

12    119            111;1112;119

14    1010          832;1010

novinosrin
Tourmaline | Level 20
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
Obsidian | Level 7

Thank you for that answer.

 

Does anyone understand why mine failed though?

novinosrin
Tourmaline | Level 20

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1085 views
  • 0 likes
  • 3 in conversation