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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: