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

4 REPLIES 4
Steelers_In_DC
Barite | Level 11

Here you go:

 

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
;run;

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;

Reeza
Super User

Please don't post the same question in multiple boards 😞

beginner
Calcite | Level 5

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!

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 580 views
  • 0 likes
  • 4 in conversation