topic Re: How to concatenate multiple variables from multiple rows in SAS Programming

How to concatenate multiple variables from multiple rows

mariko5797 — Wed, 06 Oct 2021 16:27:04 GMT

I think it's easiest to describe my goal by showing the type of data I have and the format I want to go into. Is there a simple way to achieve this? Thank you in advance!

Dummy dataset to use... NOTE: I have zero clue on how to add missing values with this, so the dummy code would need to be tweaked. Sorry

data dummy;
input id incexc1 $9. type1 $ incexc2 $9. type2 $;
cards;
1 INCLUSION IN102 INCLUSION IN103
1 EXCLUSION EX103 INCLUSION IN112
2 INCLUSION IN106 
3 INCLUSION IN109 EXCLUSION EX106
3 INCLUSION IN102 
3 EXCLUSION EX104 
4 INCLUSION IN107 INCLUSION IN110
5 EXCLUSION EX106 INCLUSION IN101
5 EXCLUSION EX108 
;
run;

My first attempt was the below code. Obviously this won't work b/c it will only concatenate what's in the same row not same ID.

data dummy;
 set dummy;
 if not missing(incexl1) and missing(incexl2) then incexcrs = trim(incexl1);
	else if missing(incexl1) and not missing(incexl2) then incexcrs = trim(incexl2);
	else if not missing(incexl1) and not missing(incexl2) then incexcrs = trim(incexl1) ||', '|| trim(incexl2);
	else incexcrs = '';
run;

Re: How to concatenate multiple variables from multiple rows

Reeza — Wed, 06 Oct 2021 16:36:58 GMT

Use the first approach outlined here (https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a) for each column - TYPE1 and TYPE2 stored into TYPE1_LIST and TYPE2_LIST (note that in SAS you cannot have variables with the same name, so I'm assuming that you have different names than shown.

Then combine them at the end by adding:

if last.ID then do;
 inc_excl_type = catt(type1_list, type2_list);
output;
end;

Re: How to concatenate multiple variables from multiple rows

ballardw — Wed, 06 Oct 2021 17:00:29 GMT

Do you expect results in that exact order?

That is much harder than providing both "types" from a single record first.

An example of a data step to read "missing" values from your "example". The dot character will be read as missing

character value if the only character encountered.

data dummy;
   input id incexc1 $9. type1 $ incexc2 $9. type2 $;
cards;
1 INCLUSION IN102 INCLUSION IN103
1 EXCLUSION EX103 INCLUSION IN112
2 INCLUSION IN106 .         .
3 INCLUSION IN109 EXCLUSION EX106
3 INCLUSION IN102 .         .
3 EXCLUSION EX104 .         .
4 INCLUSION IN107 INCLUSION IN110
5 EXCLUSION EX106 INCLUSION IN101
5 EXCLUSION EX108 .         .
;

I don't know what you copied from to post that dummy data step but please review the next time you post one. What ever source seems not to have a proper end of line character.

Card (or Datalines same thing) must appear on a line by itself as does the ; or ;;;; (with Cards4) to end the data.

Re: How to concatenate multiple variables from multiple rows

mariko5797 — Wed, 06 Oct 2021 17:20:18 GMT

Thank you!

And I have zero clue why, but posting SAS code in SAS Community doesn't always keep my "next" lines if that makes sense. My code will be on multiple lines, but will post as one. I didn't know 'cards' had to be on its own line though (I always just blindly copied it that way), so good to know!

Re: How to concatenate multiple variables from multiple rows

Reeza — Wed, 06 Oct 2021 17:34:05 GMT

Typically this happens if you edit the code after you've posted in the code box but not in the code editor.