topic Re: Combining multiple rows & counting in SAS Procedures

Combining multiple rows & counting

beginner — Thu, 21 Jan 2016 21:35:09 GMT

Hi, I'm new to SAS, and apologize if this question has been asked elsewhere. I have been searching previous messages for this type of work and couldn't find what I wanted. I have a dataset that contains multiple rows per patient. I'd like to combine all the rows for each patient, and also create an additional column with a count of how many rows were combined.

So, for example, I'm starting with a dataset that looks like this:

patient fruit color

patientA apple red

patientA banana red

patientA mango red

patientB apple blue

patientC cherry green

patientC grape green.

And I want to reorganize into a dataset that looks something like this:

patient count fruit_1 fruit_2 fruit_3 color

patientA 3 apple banana mango red

patientB 1 apple (blank) (blank) blue

patientC 2 cherry grape (blank) green.

Thank you in advance for your help. I have been struggling with this for days, trying combinations of different lines I've been piecing together from searches.

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 21:43:56 GMT

You're looking to transpose your data set. You can use proc transpose or a data step and then you can count the number of missing/non missing to get your number of rows.

Here's two links with examples of transposing your data from a wide to a long format.

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Re: Combining multiple rows & counting

FreelanceReinh — Thu, 21 Jan 2016 21:50:05 GMT

Try this:

data have;
input patient $ fruit $ color $;
cards;
patientA apple  red
patientA banana red
patientA mango  red
patientB apple  blue
patientC cherry green
patientC grape  green
;

proc transpose data=have prefix=fruit_ out=trans(drop=_name_);
by patient color;
var fruit;
run;

proc freq data=have noprint;
tables patient / out=cnt(drop=percent);
run;

data want;
merge trans cnt;
by patient;
run;
      
proc print data=want;
var patient count fruit_: color;
run;

Re: Combining multiple rows & counting

beginner — Thu, 21 Jan 2016 21:50:19 GMT

Thanks so much for your fast reply, Reeza. Sorry if this is a naive question, but how do this if my data is already in SAS as a SAS spreadsheet? In the example you linked for the transposition, I would be typing in data as part of the command(s), right? My dataset is really big (like 10s of columns), so I don't want to be reentering the data.

Re: Combining multiple rows & counting

FreelanceReinh — Thu, 21 Jan 2016 22:12:50 GMT

I've included the first data step just to create a test dataset for demonstration. With your existing dataset you would start at the PROC TRANSPOSE step.

The work dataset TRANS created by PROC TRANSPOSE already contains everything you want, except for variable COUNT. The frequency counts are created by PROC FREQ and written to the output dataset of this procedure, which I named CNT (and which originally contained an additional variable PERCENT, but I dropped this one, because you didn't request percentages).

Variable COUNT and the variables from dataset TRANS are merged in the data step creating dataset WANT, using the common variable PATIENT (i.e. common to TRANS and CNT) as a key to identify matching observations.

Finally, I used PROC PRINT just to show the final result. The purpose of the VAR statement was to obtain the same column order in the output as in your example table. The internal order of the variables in the dataset is left unchanged (and irrelevant in most cases).

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 21:59:46 GMT

The first step is making the fake data.

In the second steps where they reference faked data, you can reference your data set.

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 22:11:08 GMT

Working of @FreelanceReinh solution, but his last three steps can be simplified into one step.

data have;
input patient $ fruit $ color $;
cards;
patientA apple  red
patientA banana red
patientA mango  red
patientB apple  blue
patientC cherry green
patientC grape  green
;

proc transpose data=have prefix=fruit_ out=trans(drop=_name_);
by patient color;
var fruit;
run;

data want;
set trans;
array fr(*) $ fruit_:;

count=dim(fr)-cmiss(of fr(*));

run;

Re: Combining multiple rows & counting

FreelanceReinh — Thu, 21 Jan 2016 22:15:48 GMT

@Reeza: Would your simplification produce the same result as my code, if there were missing values of FRUIT in the HAVE dataset?

Re: Combining multiple rows & counting

beginner — Thu, 21 Jan 2016 22:21:50 GMT

Thank you, FreelanceReinhard. How should I change the syntax, if I decide to remove the "color" variable from my dataset? (E.g., will the : followed by a ; cause problems?)

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 22:22:52 GMT

Drop color from proc transpose and everything should be fine. Unless you have multiples of your fruits...then you'll have an issue.

Re: Combining multiple rows & counting

FreelanceReinh — Thu, 21 Jan 2016 22:30:19 GMT

The ":;" does not cause any problems. So, please feel free to delete "color" from the PROC TRANSPOSE and the PROC PRINT step.

@Reeza: What do you mean by "multiples of your fruits"? Duplicate values?

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 22:45:58 GMT

@FreelanceReinh Yes to duplicates.

Missing at the data level may cause issues but I'm assuming they could easily be filtered out with a where clause.

Re: Combining multiple rows & counting

FreelanceReinh — Thu, 21 Jan 2016 23:17:04 GMT

Maybe I'm too tired to see, but how would duplicate FRUIT values (or any other content of this variable, for that matter) interfere with the transposing?

Re: Combining multiple rows & counting

Reeza — Thu, 21 Jan 2016 23:27:28 GMT

@FreelanceReinh Nope, I'm too tired....I was thinking of the case when the fruit became the header, which isn't occuring in this case. So there's no issue and your code is correct 🙂

Re: Combining multiple rows & counting

Ksharp — Fri, 22 Jan 2016 02:50:13 GMT

data have;
input patient $ fruit $ color $;
cards;
patientA apple  red
patientA banana red
patientA mango  red
patientB apple  blue
patientC cherry green
patientC grape  green
;
proc sql;
 select max(n) into : n separated by ' '
  from (select count(*) as n from have group by patient,color);
quit;
data want;
 set have;
 by patient color;
 array x{*} $ 40 fruit1-fruit&n;
 retain fruit1-fruit&n;
 if first.color then do;n=0;call missing(of x{*});end;
 n+1;
 x{n}=fruit;
 if last.color;
 drop fruit;
run;

Re: Combining multiple rows & counting

karthikC — Fri, 22 Jan 2016 04:30:08 GMT

Just in case if you require only the number of combined rows for the variable Patient ID, below code can be handy.

data pat1;

set pat;

if first.patient then count =1;

else count+1;

if last.patient;

patient;

run;

for Output, just click on the attachement.

Thanks

Re: Combining multiple rows & counting

beginner — Fri, 22 Jan 2016 22:20:59 GMT

Thanks to all for your help! I haven't been able to try everything you've suggested yet, but the parts I've tried so far are very helpful. 🙂

Re: Combining multiple rows & counting

beginner — Mon, 25 Jan 2016 19:54:02 GMT

Hi, FreelanceReinhard (or others). I think I might be encountering the problem you & Reeza discuss here. If I have different values of color for any 1 patient, how would you recommend handling the situation? In other words, is there a way to transpose multiple variables simultaneously (i.e., create new fruit_n AND create color_n columns)?

Re: Combining multiple rows & counting

FreelanceReinh — Mon, 25 Jan 2016 20:45:02 GMT

Hi @beginner,

Yes, there are several different approaches to transpose more than one variable. I learned (what I found to be) the most elegant one -- using PROC SUMMARY's IDGROUP option -- only ten days ago from @Ksharp's posting here:

https://communities.sas.com/t5/General-SAS-Programming/getting-multiple-rows-of-dates-for-a-subject-into-a-single-row/m-p/243643#M35172.

I can write up the code if you like.

Edit:

Here it is (borrowing the PROC SQL idea again from @Ksharp, now from the current thread):

data have;
input patient $ fruit $ color $;
cards;
patientA apple  red
patientA banana pink
patientA mango  magenta
patientB apple  blue
patientC cherry green
patientC grape  lime
;

proc sql noprint;
select max(n) into :n
from (select count(*) as n from have group by patient);
quit;

proc summary data=have;
by patient;
output out=want(drop=_type_ rename=(_freq_=count)) idgroup(out[&n] (fruit color)=);
run;

proc print data=want;
run;

As an additional benefit, PROC SUMMARY computes the frequencies by default (in variable _FREQ_). So, we need neither PROC TRANSPOSE for the transposing nor PROC FREQ for the counting.

Re: Combining multiple rows & counting

Reeza — Mon, 25 Jan 2016 20:40:30 GMT

If you're transposing multiple variables, this macro may be of interest.

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset