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

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

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

 

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20
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;
SAS_AMUH
Obsidian | Level 7

@

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

 

SAS_AMUH
Obsidian | Level 7

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

 

 

andreas_lds
Jade | Level 19

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;

 

SAS_AMUH
Obsidian | Level 7

I appreciate your answer. I will learn about the CATS and  CATX functions. 

Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1478 views
  • 3 likes
  • 4 in conversation