@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;
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
The first step is making the fake data.
In the second steps where they reference faked data, you can reference your data set.
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;
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. 🙂
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;
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
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 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.