Solved
Contributor
Posts: 43

# how to do an iterative loop in proc sql;

Hello,

I'm trying to write a simple do loop where it'll iterate over a table a few times and create a new table. My logic is as follows

``````%let K=1 to 2 by .25;
Proc sql;
Create table as TableAt_K
SELECT
Column,
Column2*k
from Table1;
quit;``````

I want my code to run from 1 to 2, and each time, it'll multiply column*k and create a new table.

Accepted Solutions
Solution
‎05-07-2018 11:34 PM
Super User
Posts: 8,215

## Re: how to do an iterative loop in proc sql;

I don't think @novinosrin's code will work, but the following should:

``````data table1;
input column column2;
cards;
5 10
6 12
5 2
;

%macro iterate;

%do K=100 %to 200 %BY 25;
%let L=%sysfunc(catt(%sysfunc(int(%eval(&k./100))),dot,
%eval(&k.-%sysfunc(int(%eval(&k./100)*100)))));
Proc sql;
Create table TableAt_&L. as
SELECT
Column,
Column2*&k
from Table1
;
quit;

%end;

%mend;

%iterate``````

Art, CEO, AnalystFinder.com

All Replies
Super User
Posts: 2,050

## Re: how to do an iterative loop in proc sql;

[ Edited ]
``````%macro iterate;

%do K=1 %to 2 %by .25;
Proc sql;
Create table as TableAt_&K
SELECT
Column,
Column2*&k
from Table1;
quit;

%end;

%mend;

%iterate/*or*/%macro iterate;Proc sql;%do K=1 %to 2 %by .25;Create table as TableAt_&KSELECTColumn,Column2*&kfrom Table1;%end;quit;%mend;%iterate``````
Solution
‎05-07-2018 11:34 PM
Super User
Posts: 8,215

## Re: how to do an iterative loop in proc sql;

I don't think @novinosrin's code will work, but the following should:

``````data table1;
input column column2;
cards;
5 10
6 12
5 2
;

%macro iterate;

%do K=100 %to 200 %BY 25;
%let L=%sysfunc(catt(%sysfunc(int(%eval(&k./100))),dot,
%eval(&k.-%sysfunc(int(%eval(&k./100)*100)))));
Proc sql;
Create table TableAt_&L. as
SELECT
Column,
Column2*&k
from Table1
;
quit;

%end;

%mend;

%iterate``````

Art, CEO, AnalystFinder.com

Contributor
Posts: 43

## Re: how to do an iterative loop in proc sql;

thank you so much!  I ran this in SAS studio and it gave me what I needed.  Can you clarify what these syntax are doing?

``````    %let L=%sysfunc(catt(%sysfunc(int(%eval(&k./100))),dot,
%eval(&k.-%sysfunc(int(%eval(&k./100)*100)))));``````

there seems to be a lot going and I dont understand the negative sign, division etc.

Also, why are my tabe name "1dot0" "1dot25" etc?

Super User
Posts: 8,215

## Re: how to do an iterative loop in proc sql;

The %let statement is simply doing what you originally asked for. Since macro do loops can't handle fractions, I used 100 to 200 by 25, rather than 1 to 2 by .25. Also, since SAS dataset names can only contain alpha, numeric and underscore characters, rather than try to create an illegal name that contained a ., I created names like table1dot0 rather than table1.0

The %let statement simply built the 1dot0, 1dot25, etc. parts of the name. The first part would take a number like 125 and reduce it to 1, the the characters 'dot' were added, followed by the fraction (e.g., 125-100=25).

Art, CEO, AnalystFinder.com

☑ This topic is solved.