I need help with combining data into one row.
I would like the <ORIGINAL> data to be displayed as <NEW>.
I believe that if I TRANSPOSE , it will result into multiple column.
Like <NEW> I would like multiple products to be displayed in a single column.
Thank you for your help in advance.
<ORIGINAL>
ID Product Days
100 A 1
101 B 2
102 A 1
102 B 1
102 C 1
103 A 5
103 A 5
<NEW>
ID Product Days
100 A 1
101 B 2
102 ABC 1
103 AA 5
If the result you want is not the final report, then i don't think, that the data-format you want is useful for most analysis i can think of.
Try (untested code):
data want;
set have(rename=(Drug = _Drug));
by Id Day;
length Drug $ 20; /* the length depends on the number of drugs/day */
retain Drug;
if first.Day then do;
Drug = ' ';
end;
Drug = catx(',', Drug, _Drug);
if last.Day then do;
output;
end;
drop _Drug;
run;
data have;
input ID Product $ Days;
datalines;
100 A 1
101 B 2
102 A 1
102 B 1
102 C 1
103 A 5
103 A 5
;
data want(drop=_:);
set have (rename=Product=_Product);
by ID;
length Product $ 200;
if first.ID then call missing(Product);
Product = cats (Product, _Product);
if last.ID;
retain Product;
run;
@draycut
Thank you so much for your reply. Your answer was really helpful for me.
But, I'm sorry I didn't write my question and example clearly.
What I exactly needed is like this below.
The current coding resulted in one ID and one observation, but each ID will have multiple days.
I would be grateful if you could show me, how to code this.
<ORIGINAL_2>
ID Product Days
100 A 1
100 A 2
100 B 2
100 C 2
101 A 1
101 A 2
<NEW_2>
ID Product Days
100 A 1
100 ABC 2
101 A 1
101 A 2
Apologies for poor wording of my question but I would like to get some help, please.
I have a large data but simplified like this below. Days represents the days from the first visit to the doctor.
This means ID Number 100 received drug A and B on the 5th day, and drug A on the 7th day.
But currently, ID Number 100 has two rows for the 5th day. What I would exactly likely to get is that if the ID gets a multiple medications on the same day, I want them to be combined into a single column.
<ORIGINAL>
ID Drug Days
100 A 5
100 B 5
100 A 7
101 C 3
101 A 4
102 D 2
102 A 2
<NEW>
ID Drug Days
100 A,B 5
100 A 7
101 C 3
101 A 4
102 D,A 2
If the result you want is not the final report, then i don't think, that the data-format you want is useful for most analysis i can think of.
Try (untested code):
data want;
set have(rename=(Drug = _Drug));
by Id Day;
length Drug $ 20; /* the length depends on the number of drugs/day */
retain Drug;
if first.Day then do;
Drug = ' ';
end;
Drug = catx(',', Drug, _Drug);
if last.Day then do;
output;
end;
drop _Drug;
run;
I appreciate your answer. I will learn about the CATS and CATX functions.
Please try the below code as well,it is slightly different from the existing posts
data have;
input ID Product $ Days;
datalines;
100 A 1
101 B 2
102 A 1
102 B 1
102 C 1
103 A 5
103 A 5
;
data want;
set have;
by id;
retain new;
if first.id then new=product;
else new=catx('',new,product);
if last.id;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.