topic Re: How to do loop for the same query on different varibles in SAS Procedures

How to do loop for the same query on different varibles

db145 — Wed, 13 Mar 2019 17:14:49 GMT

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!

Re: How to do loop for the same query on different varibles

SASJedi — Wed, 13 Mar 2019 21:05:29 GMT

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)

Re: How to do loop for the same query on different varibles

PGStats — Thu, 14 Mar 2019 04:49:00 GMT

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;

Re: How to do loop for the same query on different varibles

db145 — Thu, 14 Mar 2019 13:37:27 GMT

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();

Re: How to do loop for the same query on different varibles

Astounding — Fri, 15 Mar 2019 13:44:34 GMT

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.