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.
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;
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.
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.
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).
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;
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)?
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:
I can write up the code if you like.
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.
Hi @FreelanceReinh. Thanks for yet another fast & super helpful reply. Just tried the @Ksharp script you pointed me to, and wow, this is great!! Thank you so much! Sorry for what is likely a dense question, but for this new scripting, where would I input the names of columns that I do not want to split into n columns (columns that have the same value for all n entries, for any 1 patient)?
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.