topic Re: Long to wide format with conditions in SAS Programming

Long to wide format with conditions

sandrube — Tue, 21 Apr 2020 18:53:58 GMT

I have the attached data set and I'm trying to convert it to wide format with conditions.There are patients who took drug1 and drug2. I want to see how many days each drug was taken and whether if was completed or discontinued. Whether it is completed or discontinued depend on the status on the last day. If it is completed on last day, it is completed. If it is discontinued on last day, it is discontinued. The final data would look like this:

data new;
infile cards missover;
input Pt_id	drug1	drugs1_days	drug1_status $ drug2 drug2_days	drug2_status $	;
cards;
1	1	5	completed		1	1	completed	
2	1	2	discontinued		1	2	completed	
3	1	1	completed		0	.	.	
4	1	3	completed		1	2	discontinued	
5	1	4	completed		0	.	.	
;
run;

Thank you for your help!

Re: Long to wide format with conditions

sustagens — Wed, 22 Apr 2020 06:09:08 GMT

/*sort*/
proc sort data=have;
by Pt_id drugs start_date end_date;
run;

/*calculate number of days, then sum by Pt_id and drug*/
proc sql;
create table days as 
select Pt_id, drugs, 
sum(intck('day',start_date,end_date)+1) as days 
from have
group by Pt_id, drugs;
quit;

/*keep last status*/
data stat (keep=Pt_id drugs stat where=(stat ne '')); 
set have;
by Pt_id drugs start_date end_date;
format stat $12.;

if last.drugs then stat=status; 
if substr(stat,1,3)="dis" then stat="discontinued";
run;

/*long to wide*/
%macro trns(var);
proc transpose  
data=&var 
out=trns_&var
prefix=&var._
;

by Pt_id;
id drugs;
var &var;
run;
%mend trns;

/*transpose one at a time*/
%trns(days);
%trns(stat);

/*combine*/
proc sql;
create table want as 
select 
t1.Pt_id,
(case when t1.days_drug1 ne . then 1 else 0 end) as drug1,
t1.days_drug1 as drug1_days,
t2.stat_drug1 as drug1_status,
(case when t1.days_drug2 ne . then 1 else 0 end) as drug2,
t1.days_drug2 as drug2_days,
t2.stat_drug2 as drug2_status 
from trns_days t1 
inner join trns_stat t2 
on t1.Pt_id = t2.Pt_id;
quit;

Re: Long to wide format with conditions

PaigeMiller — Wed, 22 Apr 2020 11:16:09 GMT

If you want the wide format for some sort of reporting, then PROC REPORT can work with your data in the format it is in, and produce a report, without YOU specifically programming a conversion of the data set to a wide format.

For just about anything else you want to do in SAS, the wide format is less effective and requires more coding than leaving the data in the long format, and so is not advised.

Re: Long to wide format with conditions

sandrube — Fri, 24 Apr 2020 12:24:52 GMT

ok, thank you!

Re: Long to wide format with conditions

sandrube — Fri, 24 Apr 2020 12:25:09 GMT

Thank you very much!