DATA Step, Macro, Functions and more

how do I replace numbers with do loop macro

Accepted Solution Solved
Reply
Super Contributor
Posts: 345
Accepted Solution

how do I replace numbers with do loop macro

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


Accepted Solutions
Solution
‎05-23-2017 12:20 PM
Trusted Advisor
Posts: 1,586

Re: how do I replace numbers with do loop macro

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.

View solution in original post


All Replies
Super User
Posts: 5,441

Re: how do I replace numbers with do loop macro

Something like that yes.
But test it with fewer loops, and with data sets with just few observations.
Data never sleeps
Super User
Posts: 5,518

Re: how do I replace numbers with do loop macro

Where are the codes stored now? 

 

Can you provide a sample?

 

Do any contain embedded blanks?

Super Contributor
Posts: 345

Re: how do I replace numbers with do loop macro

Posted in reply to Astounding

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); 
Super User
Posts: 11,343

Re: how do I replace numbers with do loop macro

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?

PROC Star
Posts: 102

Re: how do I replace numbers with do loop macro


wenling 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

Trusted Advisor
Posts: 1,586

Re: how do I replace numbers with do loop macro

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.

Super Contributor
Posts: 345

Re: how do I replace numbers with do loop macro

[ Edited ]

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.

Super User
Posts: 11,343

Re: how do I replace numbers with do loop macro


wenling 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.

Super Contributor
Posts: 345

Re: how do I replace numbers with do loop macro

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

Super User
Super User
Posts: 7,083

Re: how do I replace numbers with do loop macro

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 ;
Trusted Advisor
Posts: 1,586

Re: how do I replace numbers with do loop macro

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);
Super Contributor
Posts: 345

Re: how do I replace numbers with do loop macro

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.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

          see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           see. a&i.&TID_NUM.

           ….

 

    

RUN;

Solution
‎05-23-2017 12:20 PM
Trusted Advisor
Posts: 1,586

Re: how do I replace numbers with do loop macro

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 254 views
  • 2 likes
  • 7 in conversation