SQL method to sum up years of duration and create a new variable

[ak2011]

Hi,

I would appreciate if someone helps me with the SQL code to sum of years of exposure to any of 3 agents(a1,a2,a3) for each id and SAS automatically creating a new variable for the total years of exposure.I have many agents so manually writing a new name for the summed-up exposures will be quite tedious.

I tested only 3 agents by using SQL procedure to calculate yrs of exposure to agents a1_yrs a2_yrs……. for each id. 2 main aims:-(i) For each id, summing up years of exposure to any of the agents a1_yrs, a2_yrs and a3_yrs and (ii)creating a new variable for the summed-up yrs. Each id may be exposed to either 1, 2 or all 3 of the agents. In brief I would need a SAS code to indicate exposure to any of the agents eg. a1 only or a2 only or a3 only, 2 of the agents eg a1 and a3 or a2 and a3,etc. So if I have eg 10 agents a1,a2,a3……… a9,a10; there will be so many combinations and creating new variables will be cumbersome. Please help me with a SAS code to create a summary total duration of exposure to any of the  agents.    Thanks very much.

ak. 

data miss;
input id$ 1-4 a1_yrs 5-6 a2_yrs 7-9 a3_yrs 10-12 level 13-14;
datalines;
a11	4 	  62 3
a12	7 3	  
a13	8 11  5  2 
a14 2 6   9  2
a15 3 3   4  
a16 3 3   4  2
a17 3     4  3
b02 6 1   3  2
b03 8 11  12 2
c09 7 2      3
c12 1     1  
c17 3 3  
;
proc print data=miss;
title 'exposure with some blanks'; run;

proc sql;
create table expbl as
  select
    id, a1_yrs, a2_yrs, a3_yrs,
    /*sum(a1_yrs) as a1_tyr,*/
   /*a1_yrs as a1_tyr,a2_yrs as a2_tyr, a3_yrs as a3_tyr,*/
    sum(a1_yrs,a2_yrs) as a1a2_tyr,
    sum(a1_yrs,a3_yrs) as a1a3_tyr,
    sum(a1_yrs,a2_yrs,a3_yrs) as a1a2a3_tyr
    /*sum(a2_yrs) as a2_tyr,
    sum(a3_yrs) as a3_tyr*/
    
  from miss
;
quit;

proc print data=expbl; title ' exp blank final results'; run;

Log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 data miss;
74 input id$ 1-4 a1_yrs 5-6 a2_yrs 7-9 a3_yrs 10-12 level 13-14;
75 datalines;

NOTE: The data set WORK.MISS has 12 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


88 ;
89 proc print data=miss;
90 title 'exposure with some blanks'; run;
NOTE: There were 12 observations read from the data set WORK.MISS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.22 seconds
cpu time 0.22 seconds


91
92 proc sql;
93 create table expbl as
94 select
95 id, a1_yrs, a2_yrs, a3_yrs,
96 /*sum(a1_yrs) as a1_tyr,*/
97 /*a1_yrs as a1_tyr,a2_yrs as a2_tyr, a3_yrs as a3_tyr,*/
98 sum(a1_yrs,a2_yrs) as a1a2_tyr,
99 sum(a1_yrs,a3_yrs) as a1a3_tyr,
100 sum(a1_yrs,a2_yrs,a3_yrs) as a1a2a3_tyr
101 /*sum(a2_yrs) as a2_tyr,
102 sum(a3_yrs) as a3_tyr*/
103
104 from miss
105 ;
NOTE: Table WORK.EXPBL created, with 12 rows and 7 columns.

106 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


107
108 proc print data=expbl; title ' exp blank final results'; run;
NOTE: There were 12 observations read from the data set WORK.EXPBL.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.15 seconds
cpu time 0.15 seconds


109
110 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
122

[Reeza · Accepted solution]

Why the limitation to SQL?

Please put your question in the main text box, not the code box and code and logs into the code boxes. I'll revise this one for you.

[ak2011]

Hi,
Thanks for your response and your advise. I thought maybe SQL could handle the problem better, but I might be wrong; there might be other ways too.

[ballardw]

I think you may want to reconsider this approach. You will be adding about 1013 variables with anything resembling that. Admittedly some, if not many, of those variable will have missing values but what will you actually do with all of those variables?

I would be tempted to examine such data before creating any pile of variables with something like:

proc format;
value putx
 0<-high = 'X'
 ;
 run;

proc freq data=have;
  tables a1_yrs*a2_yrs*a3_yrs / list missing; /*<= list all the yearly variables here*/
  format a1_yrs a2_yrs a3_yrs putx.; /* and here as well*/
run;

This will give you a table of combinations with the variable names at the top and X where there was any value greater than 0 for the year. So you at least can see how many actual combinations occur in your data.