Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- How to do loop for the same query on different varibles

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-13-2019 01:14 PM
(2206 views)

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!

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

PG

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

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.