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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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;

View solution in original post

21 REPLIES 21
Reeza
Super User

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

beginner
Calcite | Level 5

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.

FreelanceReinh
Jade | Level 19

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).

Reeza
Super User

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;
FreelanceReinh
Jade | Level 19

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

beginner
Calcite | Level 5

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?)

Reeza
Super User

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

FreelanceReinh
Jade | Level 19

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?

Reeza
Super User

@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. 

FreelanceReinh
Jade | Level 19

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?

Reeza
Super User

@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 🙂

beginner
Calcite | Level 5

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)?

 

FreelanceReinh
Jade | Level 19

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-....

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.

beginner
Calcite | Level 5

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)?

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 1429 views
  • 0 likes
  • 5 in conversation