topic how to do an iterative loop in proc sql; in SAS Programming

how to do an iterative loop in proc sql;

mrdlau — Sun, 06 May 2018 22:59:26 GMT

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.

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

novinosrin — Sun, 06 May 2018 23:35:54 GMT

%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

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

art297 — Mon, 07 May 2018 00:14:47 GMT

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

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

mrdlau — Tue, 08 May 2018 03:37:11 GMT

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?

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

art297 — Tue, 08 May 2018 03:57:16 GMT

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