Long to wide format with conditions

[sandrube]

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!

[sustagens · Accepted solution]

/*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;

[sandrube]

Thank you very much!

[PaigeMiller]

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.

[sandrube]

ok, thank you!