topic Re: how do I replace numbers with do loop macro in SAS Programming

how do I replace numbers with do loop macro

Bal23 — Thu, 18 May 2017 19:01:26 GMT

DATA  see.a233&Tid_num.;
	SET abb.abb_233;
	WHERE 	v1 = "&n"
	OR 		v2 = "&m";

	
RUN;

I need to copy and paste a series of above code, from 233 to 459. I think it would be better to use a do loop instead of copying and pasting with so many similar lines. ANy one gives an advice?

that will be

%macro yrmo(N)

%do I= 233 %To &N;

%end;

%mend yrmo;

%yrmo(459);

right?

Since each has lots of obs, it will be better to get advince from you first. Thank you

Re: how do I replace numbers with do loop macro

LinusH — Thu, 18 May 2017 19:31:10 GMT

Something like that yes.
But test it with fewer loops, and with data sets with just few observations.

Re: how do I replace numbers with do loop macro

Astounding — Thu, 18 May 2017 19:54:32 GMT

Where are the codes stored now?

Can you provide a sample?

Do any contain embedded blanks?

Re: how do I replace numbers with do loop macro

Bal23 — Thu, 18 May 2017 20:02:50 GMT

I do not quite understand. I do not specificaly save it elsewhere. No embeded blanks. below is a kind of sample code.

%macro yrmo(N) 


%do I= 233%To &N; 

DATA see.a233&Tid_num.; 
SET abb.abb_233; 
WHERE v1 = "&nac" OR v2 = "&mac"; 
RUN;
 %end; 
%mend yrmo; 
%yrmo(459);

Re: how do I replace numbers with do loop macro

ballardw — Thu, 18 May 2017 20:10:23 GMT

You have an awful lot of macro variables and called macro that are not defined anywhere.

And you are not using the &I from the macro loop anywhere so at this point all your a doing is calling the same data step over and over again.

Can you show us a NON-macro version of the code that does what you want for one example? And then point to what should change inside a %do loop?

Re: how do I replace numbers with do loop macro

s_lassen — Fri, 19 May 2017 06:36:32 GMT

@Bal23 wrote:
I do not quite understand. I do not specificaly save it elsewhere. No embeded blanks. below is a kind of sample code.
%macro yrmo(N) 


%do I= 233%To &N; 

DATA see.a233&Tid_num.; 
SET abb.abb_233; 
WHERE v1 = "&nac" OR v2 = "&mac"; 
RUN;
 %end; 
%mend yrmo; 
%yrmo(459); 

I think you just need to replace the number 233 with your macro loop variable:

%macro yrmo(N) 
%local i;
%do I=233 %To &N; 
  DATA see.a&i.&Tid_num.; 
    SET abb.abb_&i.; 
    WHERE v1 = "&nac" OR v2 = "&mac"; 
  RUN;
  %end; 
%mend yrmo; 
%yrmo(459);

The period after &i is to tell SAS where the name of the macro variable ends, it is needed when there is no space or other delimiter after the reference to the macro variable.

Notice that I also declared the I variable %local to the macro, may save you some debugging time later. And finally, you needed a space before %to in your macro code, which I added.

Regards,

Søren

Re: how do I replace numbers with do loop macro

Shmuel — Fri, 19 May 2017 16:09:34 GMT

Is this your last code ?

%macro yrmo(N) 
   %do I= 233  %To &N; 
         DATA see.a233&Tid_num.; 
           SET abb.abb_233; 
                  WHERE v1 = "&nac" OR v2 = "&mac"; 
          RUN;
    %end; 
%mend yrmo; 
%yrmo(459);

The only macro variable you have supplied is N.

You defined macro variable I (in the %DO loop) but never used.

You are using macro variables: &nac and &mac - but you did not show assigning values to them.

Sappose you meant to do:

%let nac = ...;  /* any given value */
%let mac = ...; /* any given value */
%macro yrmo(N) 
   %do I= 233  %To &N; 
         DATA see.a&i.&Tid_num.; 
           SET abb.abb_&i; 
                  WHERE v1 = "&nac" OR v2 = "&mac"; 
          RUN;
    %end; 
%mend yrmo; 
%yrmo(459);

Did you mean replace names of input and its output datasets, then this might work and run on:

adb.adb_233, adb.adb_234, adb.adb_235, ... adb.adb_459.

Re: how do I replace numbers with do loop macro

Bal23 — Mon, 22 May 2017 13:16:54 GMT

Thank you. We are almost there, and then I realized that was my mistake.

from 233 to 459, the list does not include all these numbers.

see, it is 233, 234,235,236,237,238;

and then, it is 333, 334, 335, 336;

and then, it is 450, 4511,452,453,...459;

should I include three loops instead of one? Sorry I had not made it clear enough for my original post.

Re: how do I replace numbers with do loop macro

ballardw — Mon, 22 May 2017 17:24:07 GMT

@Bal23 wrote:

Thank you. We are almost there, and then I realized that was my mistake.

from 233 to 459, the list does not include all these numbers.

see, it is 233, 234,235,236,237,238;

and then, it is 333, 334, 335, 336;

and then, it is 450, 4511,452,453,...459;

should I include three loops instead of one? Sorry I had not made it clear enough for my original post.

An irregular set of values such as you show may work better by creating a control data set that has the values you need as one record per value and then use a data null step with call execute.

Your other undefined macros may cause problems but something similar to

data control;
   do value =  233, 234,235,236,237,238,333, 334, 335, 336,450, 451,452,453 to 459;
   output;
   end;
run;


data _null_;
   set control;
   length str $ 100;
   str = cats('DATA see.a',value,'&Tid_num.;');
   call execute (str);
   str = cats('SET abb.abb_',value,';');
   call execute (str);
   call execute ('WHERE v1 = "&nac" OR v2 = "&mac"; 
   RUN;');
run;

may do what you are attempting if I interpretted all of the places you are using those values correctly.

Note that you would not need the macro YRMO at all. The single quotes are intentional to keep the macro values from attempting to

resove while the data _null_ step is compiling.

If your Tid_num, nac or mac macro variables are supposed to be somehow connected to the values of the control variable that has not been mentioned up til now.

Re: how do I replace numbers with do loop macro

Tom — Mon, 22 May 2017 17:48:32 GMT

The data step DO statement is much more powerful than the macro %DO statement.

Perhaps you can generate the code using a data step instead of a macro?

do i=233 to 238, 333 to 336, 450 to 459 ;

Re: how do I replace numbers with do loop macro

Bal23 — Mon, 22 May 2017 18:04:50 GMT

yes, thank you. I got some error messages about the code above

Re: how do I replace numbers with do loop macro

Shmuel — Mon, 22 May 2017 18:05:08 GMT

If you want to use the supplied macro but skip unexisting input dataset, you can do:

%let nac = ...;  /* any given value */
%let mac = ...; /* any given value */
%macro yrmo(N) 
   %do I= 233  %To &N; 
       %if %sysfunc(exist(abb.abb_&i)) %then %do;
         DATA see.a&i.&Tid_num.; 
           SET abb.abb_&i; 
                  WHERE v1 = "&nac" OR v2 = "&mac"; 
          RUN;
       %end;
    %end; 
%mend yrmo; 
%yrmo(459);

Re: how do I replace numbers with do loop macro

Bal23 — Mon, 22 May 2017 18:43:36 GMT

That is great. Thank you. my next step will be to put all these datasets generated into one single file, something like below. I do not know how to do it efficiently instead of copy and pasting....

DATA see.bl&TID_NUM. ;

SET see. a&i.&TID_NUM.

see. a&i.&TID_NUM.

….

RUN;

Re: how do I replace numbers with do loop macro

Shmuel — Mon, 22 May 2017 19:35:00 GMT

You cannot use same input name and mean different input datasets (all are: see. a&i.&TID_NUM).

In previous code you have selected observation (criteria - WHERE v1 = "&nac" OR v2 = "&mac";)

creating new set of datasets with a new name (new suffix).

Do you realy need all discrete files or just the unified one ?

If unified is enough do it in one, same step:

%let nac = ...;  /* any given value */
%let mac = ...;  /* any given value */
%let Tid_num = ... ;  /* any given value */
%macro yrmo(N);
    DATA see.bl&Tid_num.;   /* unified output dataset name */
      SET
   %do I= 233  %To &N;    
            /* generating list of existing datasets */ 
            %if %sysfunc(exist(abb.abb_&i)) %then %str( abb.abb_&i );
    %end; 
      ;  /* closing SET list of datasets */
     
             WHERE v1 = "&nac" OR v2 = "&mac"; 
    RUN;       %end;
%mend yrmo; 
%yrmo(459);

If you are not satisfied with results or have any issues the code please supply the full log.