Hello,
I am trying to create a loop that will run through the same SQL code that generates different values based on the variable listed.
proc sql;
create table Univariate_VARIABLE. as
select VARIABLE,
sum(technical_premium) as TP,
sum(modeled_loss_costs_uncap_targ) as PPxCAT,
sum(loss_total) as PPwCAT,
sum(resprem_actuarial) as prem,
sum(ehy) as count,
calculated PPwCAT - calculated PPxCAT as CAT,
calculated TP - calculated PPwCAT as Other,
sum(LIAB_amt_bpmdl_wtd) as liab
from DATASET
group by VARIABLE;
quit;
I am trying to get the code so I can list a bunch of different variables in the original data set and the code will loop through doing the calculations for each variable.
Thanks for your help!
I think the easiest approach would be to use a macro to iterate the program code for the variables you specify. Something like this should do the trick:
/* Make some data to play with */
data mytable;
input V1:$2. V2:$2. V3:$2. V4:$2. V5:$2. ;
call streaminit (12345);
technical_premium=ROUND(RAND('UNIFORM',10,2));
modeled_loss_costs_uncap_targ=ROUND(RAND('UNIFORM',10,2));
loss_total=ROUND(RAND('UNIFORM',10,2));
resprem_actuarial=ROUND(RAND('UNIFORM',10,2));
ehy=ROUND(RAND('UNIFORM',10,2));
LIAB_amt_bpmdl_wtd=ROUND(RAND('UNIFORM',10,2));
datalines;
A B C D E
B C D E F
B D E F A
C E F A B
D F A B C
E A B C D
F B C E F
A B C D E
B C D E F
B D E F A
C E F A B
D F A B C
E A B C D
F B C E F
;
/**************************************************************
Use a macro for looping the process
Macro accepts 2 parameters:
1. dataset name
2. space-delimited list of variables
**************************************************************/
%macro MakeMyTable(dataset,vars);
%local variable count;
/* Scan through the list of variables, one at a time */
%let count=1;
%let variable=%scan(&vars,&count);
proc sql;
/* If we have a variable name, do the SQL */
%do %while (&variable ne );
create table Univariate_&VARIABLE as
select &VARIABLE
,sum(technical_premium) as TP
,sum(modeled_loss_costs_uncap_targ) as PPxCAT
,sum(loss_total) as PPwCAT
,sum(resprem_actuarial) as prem
,sum(ehy) as count
,calculated PPwCAT - calculated PPxCAT as CAT
,calculated TP - calculated PPwCAT as Other
,sum(LIAB_amt_bpmdl_wtd) as liab
from &DATASET
group by &VARIABLE
;
/* Get the next variable from the list */
%let count=%eval(&count+1);
%let variable=%scan(&vars,&count);
%put _local_;
%end;
quit;
%mend;
/**************************************************************
Use the macro to run the process on mytable for variables
V1 V3 and V5:
**************************************************************/
%makemytable(mytable,V1 V3 V5)
An alternative approach without macro coding, with proc summary:
%let groupVars = chol_status -- smoking_status;
/* Get the sums for different groupings */
proc summary data=sashelp.heart;
class &groupVars.; ways 1;
var height -- systolic;
output out=test sum= / autoname;
run;
/* Reformat the data and calculate new values */
data want;
set test;
length groupVar $32 groupValue $64;
array _a &groupVars.;
do i = 1 to dim(_a);
if not missing(_a{i}) then do;
groupVar = vname(_a{i});
groupValue = _a{i};
leave;
end;
end;
Diff_Sum = Height_Sum - Weight_Sum;
drop i &groupVars. _type_ _freq_;
run;
Thank you both for you replies! After looking at your codes and speaking with others in the office we were able to create code to do what I was looking for. Below is the code for reference.
%let varlist =
Variable1
Variable2
Variable3
Variable4
Variable5;
%macro Univariate();
%local i univ_variable ;
%do i=1 %to %sysfunc(countw(&varlist));
%let univ_variable = %scan(&varlist, &i);
%put &univ_variable;
proc sql;
create table Univariate_&univ_variable. as
select &univ_variable.,
sum(technical_premium) as TP,
sum(modeled_loss_costs_uncap_targ) as PPxCAT,
sum(loss_total) as PPwCAT,
sum(resprem_actuarial) as prem,
sum(ehy) as count,
calculated PPwCAT - calculated PPxCAT as CAT,
calculated TP - calculated PPwCAT as Other,
sum(LIAB_amt_bpmdl_wtd) as liab
from DATASET
group by &univ_variable.;
quit;
%end;
%mend;
%Univariate();
You've done well. I would support moving two statements:
proc sql;
moves up, to just after the %local statement
quit;
moves down, to just after the %end statement
That way you get one PROC SQL with a separate CREATE statement for each variable. As the program stands now, you get a separate PROC SQL for each variable. It's a small change, and not really necessary. But it illustrates a better understanding of what macro language does, which may come in handy in the future.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.