Do loop with unequal increment

Question

I have a lot of sas dataset files that starts with "s" followed by some numbers like s1 s7 s13 s32 etc. (I have a lot). For each files I run a set of codes, save my last result and then restart with with the following dataset file. Normally, when my dataset file numbers are sequential like s1 s2 s3 s4 s5...it is easy to use for instance a %do i=1 %to 120. But what if I want to do a loop with unequal increments? How can I do this? Like for instance a %do i=1 7 13 32.... This is quite easy in MATLAB but in SAS I am not too sure how.

Jay Corbett · Answer 1 · 2012-07-02T11:43:24.140

3

Make your %do from the lowest data set number to highest. Then just check to see if the data set exists.

%Let lib=yourlib;
%Macro Process;

 %Do i = 1 %to 1000;
  %if %sysfunc(exist(&lib..s&i)) %then %do;

    <your code>

  %end;

 %end;

%Mend process;
%process

edited Jul 02 '12 at 11:43

answered Jul 02 '12 at 11:21

Jay Corbett

28,091
21
57
74

your code will be perfect for another batch of library files that I will have to go through. Thanks a lot! – Plug4 Jul 02 '12 at 19:39

score 1 · Answer 2 · answered Jul 02 '12 at 08:47

The example below takes data sets S1, S3 and S10 and processes them, outputting data sets NEW1, NEW3 and NEW10:

/* Example data sets */
data s1 s3 s10;
run;

%macro mymac;

  /* Create 'array' of macro variables DSET1, DSET2...up to a max of DSET9999.
     Each DSETx macro variable will contain a data set number. */
  proc sql noprint;
    select compress(memname,"sS") into :dset1-:dset9999
    from sashelp.vtable
    where libname="WORK" and upcase(substr(memname,1,1))="S"
          and input(compress(memname,"sS"),8.) ne .;
  quit;
  %let numdsets=&sqlobs;

  /* Process data sets */    
  %do i=1 %to &numdsets.;
    data new&&dset&i.;
      set s&&dset&i.;
    run;
  %end;
%mend mymac;
%mymac

score 1 · Answer 3 · answered Jul 02 '12 at 09:31

You can do non-sequential increments in a normal do loop, but not in a macro do loop the same way. There is a workaround on the SAShelp website.

http://support.sas.com/kb/26/155.html

I would first put the numbers in a comma separated macro list using the following code. The prxmatch function searches for dataset names that begin with an 'S', followed by a digit(s). Once you have this, you can follow the code on the SAS website.

proc sql noprint;
select compress(upper(memname),'S') into :ds_num separated by ',' from dictionary.tables
where libname='WORK' and prxmatch("/S\d+/",upper(memname));
quit;

%put &ds_num.;

score 1 · Accepted Answer · answered Jul 02 '12 at 10:00

1

%SCAN is your friend here. Take a look at the example code below -

%let nums = 1 2 4 6 13 29 32;

%macro iter();

%let cnt = %sysfunc(countc(&nums," "));
%let cnt = %eval(&cnt + 1);
%put &cnt;

%do i = 1 %to &cnt;
%put I will now process dataset s%scan(&nums,&i," ");
%end;

%mend iter;

%iter()

You can modify the part %put... to write a DATA step.

answered Jul 02 '12 at 10:00

Mozan Sykol

367
1
9

can you please explain what goes behind `%let cnt = %sysfunc(countc(&nums," ")); %let cnt = %eval(&cnt + 1);` from what I understand is that you first count the total numbers / entry in `%let nums` but why the `&cnt+1`? – Plug4 Jul 02 '12 at 19:54
1

Good thing you asked! It's because, as written, the COUNTC function is telling you how many blanks are in the string NUMS. As long as your NUMS macro variable has exactly one blanks between tokens, you need to add one to see how many tokens exist. But be careful: for example, `%let nums = 1 2;` has four blanks (so CNT would be five). [I can't seem to get this note to display consecutive blanks in the example for the %let stmt.] – BellevueBob Jul 02 '12 at 21:13
1

@BobDuell, Good point. Yes, COUNTC in general is used to count how many times a particular character appears in a string. Here, we have to use it in conjunction with %SYSFUNC as we are applying it to a macro variable. In this case, the %LET nums part has to be written carefully to ensure there are no extra blanks or the other option is to use COMPBL (again along with sysfunc) for an additional check. I personally prefer pipes, and would always write it as %let nums = 1|2|3; – Mozan Sykol Jul 03 '12 at 01:25

score 1 · Answer 5 · answered Jul 06 '12 at 21:57

This is an update for Mozan's answer above. I do not recommend using CountC to count the elements in a list. Use CountW instead. Note update below:

%let nums = 1 2 4 6 13 29 32;

%macro iter();

%let cnt = %sysfunc(CountW(&nums));
%put &cnt;

%do i = 1 %to &cnt;
  %put I will now process dataset s%scan(&nums,&i);
%end;

%mend iter;

%iter()

Do loop with unequal increment

5 Answers5