topic Combining and extracting rows in SAS Programming

How to combine rows with a condition?

SAS_AMUH — Thu, 02 Apr 2020 06:32:34 GMT

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.

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

Re: How to combine rows with a condition?

PeterClemmensen — Thu, 02 Apr 2020 07:00:12 GMT

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;

Re: How to combine rows with a condition?

SAS_AMUH — Fri, 03 Apr 2020 01:50:33 GMT

@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

Combining and extracting rows

SAS_AMUH — Fri, 03 Apr 2020 06:13:28 GMT

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.

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

Re: Combining and extracting rows

andreas_lds — Fri, 03 Apr 2020 06:28:34 GMT

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;

Re: Combining and extracting rows

SAS_AMUH — Fri, 03 Apr 2020 08:15:26 GMT

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

Re: How to combine rows with a condition?

Jagadishkatam — Fri, 03 Apr 2020 08:57:58 GMT

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;