BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bal23
Lapis Lazuli | Level 10
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

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

13 REPLIES 13
LinusH
Tourmaline | Level 20
Something like that yes.
But test it with fewer loops, and with data sets with just few observations.
Data never sleeps
Astounding
PROC Star

Where are the codes stored now? 

 

Can you provide a sample?

 

Do any contain embedded blanks?

Bal23
Lapis Lazuli | Level 10

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); 
ballardw
Super User

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?

s_lassen
Meteorite | Level 14

@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

Shmuel
Garnet | Level 18

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.

Bal23
Lapis Lazuli | Level 10

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.

ballardw
Super User

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

Bal23
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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 ;
Shmuel
Garnet | Level 18

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);
Bal23
Lapis Lazuli | Level 10

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;

Shmuel
Garnet | Level 18

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.

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
  • 13 replies
  • 2216 views
  • 2 likes
  • 7 in conversation