BookmarkSubscribeRSS Feed
ak2011
Fluorite | Level 6
Spoiler
 

Hi,

I would appreciate if someone could help me solve this problem with SAS.

 

From dataset  t1 below, for each id_job,  I need to calculate the total duration of years of exposure (dur_yrs) for each agent, a1_exp and a2_exp. I have several others, a3_exp, a4_exp……….. I am just using these two as tests.

The 1’s are exposures and the blanks are un-exposures.

The dataset, SAS code  and log  are found below: SAS output are attached.

data t1;
input id$ 1-6 id_job$ 7-13 dur_yrs 17-19 a1_exp 21-22 a2_exp 24-25;
datalines;
osa23 osa23_1 7 1
osb17 osb17_1 46 1 1
osb33 osb33_1 16 1
osb33 osb33_2 16 1
osb44 osb44_1 3 1
osb50 osb50_2 4 1 1
osb50 osb50_2 3 1 1
osb67 osb67_2 46 1 1
osb68 osb68_2 18
osb68 osb68_3 18 1
osb68 osb68_3 30 1
osb73 osb73_2 11 1
osc16 osc16_2 4 1
osc50 osc50_2 14 1 1
osc50 osc50_3 14 1 1
;

proc freq data=t1;
tables
a1_exp*dur_yrs
a2_exp*dur_yrs;
run;

 

 

I am finding total years of exposure for a1_exp: i.e. 7+46+3+4+3+46+11+4+14+14 =152 years of exposure. I used proc. freq but it could not compute the overall total exposure for me for a1_exp and a2_exp.

I want to create a new variable for the totals for a1_exp(a1_durtotal) and a2_exp(a2_durtotal) and print them out in a table: ie. id  id_job……. a1_exp a1_durtotal a2_exp a2_durtotal.

 

Thanks in advance for your expert help.

5 REPLIES 5
blueskyxyz
Lapis Lazuli | Level 10

proc

proc sql;
	select sum(dur_yrs) as a1_durtotal 
	from t1
	where a1_exp=1;

	select sum(dur_yrs) as a2_durtotal 
	from t1
	where a2_exp=1;
quit;

data test;
	set t1;
	if a1_exp=1 then a1_durtotal+dur_yrs;
	if a2_exp=1 then a2_durtotal+dur_yrs;
	put a1_durtotal=   a2_durtotal=;
run;

freq  has no sum function, you can use  data step or proc sql to get total number

ak2011
Fluorite | Level 6
Hi,
Thanks very much for your explanation. I really like the sql method but instead of a total duration of 152 yrs for a1_exp, sql gave an output of 250 and instead of 207 yrs for a2_exp, sql gave an output of the same 250 I think there is an error somewhere.. SAS warning was that this SAS global statement is not supported in SQL.
Can you please help solve this problem again? Thanks.




OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 data t1;
74 input id$ 1-6 id_job$ 7-13 dur_yrs 17-19 a1_exp 21-22 a2_exp 24-25;
75 datalines;

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


91 ;
92 proc print;
93 run;

NOTE: There were 15 observations read from the data set WORK.T1.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.18 seconds
cpu time 0.19 seconds


94
95 proc sql;
96 select sum(dur_yrs) as a1_durtotal
97 from t1;
98 where a1_exp=1;
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
99
100 select sum(dur_yrs) as a2_durtotal
101 from t1;
102 where a2_exp=1;
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
103 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds


104
105 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
117
blueskyxyz
Lapis Lazuli | Level 10
which sas version you use?

There is no warning, use the PC SAS. Perhaps you can use another method.
ak2011
Fluorite | Level 6
Thank you! Problem resolved. There was an issue with the version.

blueskyxyz
Lapis Lazuli | Level 10
Welcome, if your problem is resolved, could you mind to accept my method as your answer.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1617 views
  • 0 likes
  • 2 in conversation