Combining rows & counting

Posts: 25

Combining rows & counting

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.

Valued Guide
Posts: 863

Re: Combining rows & counting

Here you go:


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

proc sql;
create table count as
select *,count(patient) as count
from have
group by patient
order by patient,fruit;

proc transpose data=count out=want(drop=_NAME_) prefix=fruit_;by patient count color;var fruit;

Super User
Posts: 23,724

Re: Combining rows & counting

Please don't post the same question in multiple boards Smiley Sad

Posts: 25

Re: Combining rows & counting

Oh, I'm sorry.  I wasn't sure what category my question fell under, and didn't realize the experts manned multiple boards (though in retrospect that seems obvious).  Thank you both for your help!

Super User
Posts: 13,542

Re: Combining rows & counting

One reason we request not posting in multiple areas is that questions about requirements and the responsed are likely to get separated across the threads and we may be working on incomplete knowledge of requirements. Also when a proposed solution is posted and you respond with a previously forgotten or missed requirement that will only be one one thread, not multiples.

Ask a Question
Discussion stats
  • 4 replies
  • 4 in conversation