topic Re: Combining rows & counting in SAS Programming

Combining rows & counting

beginner — Thu, 21 Jan 2016 21:39:55 GMT

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.

Re: Combining rows & counting

Steelers_In_DC — Thu, 21 Jan 2016 22:06:24 GMT

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;

Re: Combining rows & counting

Reeza — Thu, 21 Jan 2016 22:13:37 GMT

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

Re: Combining rows & counting

beginner — Fri, 22 Jan 2016 22:59:54 GMT

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!

Re: Combining rows & counting

ballardw — Fri, 22 Jan 2016 23:48:45 GMT

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.