BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sandrube
Fluorite | Level 6

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sustagens
Pyrite | Level 9
/*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;

View solution in original post

4 REPLIES 4
sustagens
Pyrite | Level 9
/*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
Fluorite | Level 6
Thank you very much!
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
sandrube
Fluorite | Level 6
ok, thank you!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 546 views
  • 0 likes
  • 3 in conversation