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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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