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.
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;
Please don't post the same question in multiple boards 😞
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: