DATA Step, Macro, Functions and more

how to do an iterative loop in proc sql;

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

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

 

View solution in original post


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_&K
SELECT
Column,
Column2*&k
from 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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 355 views
  • 0 likes
  • 3 in conversation