BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrdlau
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

4 REPLIES 4
novinosrin
Tourmaline | Level 20
%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
art297
Opal | Level 21

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

 

mrdlau
Obsidian | Level 7

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?

art297
Opal | Level 21

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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