Combining multiple rows & counting

[FreelanceReinh]

---

@beginner wrote:

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

---

You can add these in the BY statement of the PROC SUMMARY step, e.g.:

by patient age country;

[Reeza]

If you're transposing multiple variables, this macro may be of interest.

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

[Reeza]

The first step is making the fake data.

In the second steps where they reference faked data, you can reference your data set.

[FreelanceReinh · Accepted solution]

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;

[beginner]

Thanks to all for your help!  I haven't been able to try everything you've suggested yet, but the parts I've tried so far are very helpful.  🙂

[Ksharp]

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 sql;
 select max(n) into : n separated by ' '
  from (select count(*) as n from have group by patient,color);
quit;
data want;
 set have;
 by patient color;
 array x{*} $ 40 fruit1-fruit&n;
 retain fruit1-fruit&n;
 if first.color then do;n=0;call missing(of x{*});end;
 n+1;
 x{n}=fruit;
 if last.color;
 drop fruit;
run;
 

[karthikC]

Hi

Just in case if you require only the number of combined rows for the variable Patient ID, below code can be handy.

data pat1;

set pat;

if first.patient then count =1;

else  count+1;

if  last.patient;

by 

patient;

run;

for Output, just click on the attachement.  

Thanks