BookmarkSubscribeRSS Feed
FP12
Obsidian | Level 7

Hi

 

I have a macro:

 

%macro ext_1;
	%let iter = 1;

	%do %while (&iter. <= &nbfile.);
		PROC SQL ; 
			SELECT filename INTO :inputfile  FROM INP_1 WHERE ROWN = &iter.; 
		QUIT;
		%put &inputfile.;
		%let iter = %eval(&iter.+1);
	%end;
%mend;

 nbfile = 37

 

My table INP_1 is like this

Capture2.PNG

 

When I run the macro:

 

%ext_1;

 

I get a visual resulkt which is ok:

Capture.PNG

 

But when I am looking at the log (see below an extract for iter from 4 to 6):

 

MLOGIC(EXT_1):  %LET (variable name is ITER)
MLOGIC(EXT_1):  %DO %WHILE(&iter. <= &nbfile.) condition is TRUE; loop will iterate again.
MPRINT(EXT_1):   PROC SQL ;
MPRINT(EXT_1):   SELECT filename INTO :inputfile FROM INP_1 WHERE ROWN = 4;
MPRINT(EXT_1):   QUIT;
MLOGIC(EXT_1):  %PUT &inputfile.
P01ADIBP_19906_201610042343_A00000AW.txt
MLOGIC(EXT_1):  %LET (variable name is ITER)
MLOGIC(EXT_1):  %DO %WHILE(&iter. <= &nbfile.) condition is TRUE; loop will iterate again.
MPRINT(EXT_1):   PROC SQL ;
MPRINT(EXT_1):   SELECT filename INTO :inputfile FROM INP_1 WHERE ROWN = 5;
MPRINT(EXT_1):   QUIT;
MLOGIC(EXT_1):  %PUT &inputfile.
P01ADIBP_19906_201610042343_A00000AW.txt
MLOGIC(EXT_1):  %LET (variable name is ITER)
MLOGIC(EXT_1):  %DO %WHILE(&iter. <= &nbfile.) condition is TRUE; loop will iterate again.
MPRINT(EXT_1):   PROC SQL ;
MPRINT(EXT_1):   SELECT filename INTO :inputfile FROM INP_1 WHERE ROWN = 6;
MPRINT(EXT_1):   QUIT;
MLOGIC(EXT_1):  %PUT &inputfile.
P01ADIBP_19906_201610042343_A00000AW.txt

 

inputfile is always

P01ADIBP_19906_201610042343_A00000AW.txt

which is the last value of my table INP_1

 

Do you know why?

 

Thanks

10 REPLIES 10
kiranv_
Rhodochrosite | Level 12

your row number logic is helping you to insert acurately but as you have same name as macrovariable, it is overwritten and Put is picking the last value. To find each value better option is to use do  i= 1 %to &count and use &&nputfile&i.

art297
Opal | Level 21

Yes! It is because you keep replacing its value. What do you want that macro variable to look like after running your macro? Or do you want multiple macro variables?

 

In short, what are you trying to achieve?

 

Art, CEO, AnalystFinder.com

FP12
Obsidian | Level 7

Thanks for both of you for the reply,

 

After this, and still inside the loop, I call another macro with inputfile in parameter.

I prefer to have only a single macrovariable than 37 (inputfile1 to inputfile37), it is easier to manage.

 

Do you know I could do with a single inputfile?

 

Thanks,

 

art297
Opal | Level 21

If your other code is within the do while loop you showed, it will get the correct values of your macro variable.

 

Art, CEO, AnalystFinder.com

 

FP12
Obsidian | Level 7

Unfortunately not, 😞 that is what I try to explain.

The "%put inputfile" is inside the macro loop (see my first message), and it is always equals to the the last table value...

art297
Opal | Level 21

Your log should be showing you the correct results as in the following example which I think is doing the same thing as you are doing:

data INP_1;
  informat filename $50.;
  input filename rown;
  cards;
PO1ADIBP_10188_201610050135_A00000AY.txt 1
PO1ADIBP_10188_201610050230_A00000C3.txt 2
PO1ADIBP_10188_2016100502225_A00000C2.txt 3
;

%let nbfile=3;
%macro ext_1;
	%let iter = 1;

	%do %while (&iter. <= &nbfile.);
		PROC SQL ; 
			SELECT filename INTO :inputfile  FROM INP_1 WHERE ROWN = &iter.; 
		QUIT;
		%put &inputfile.;
		%let iter = %eval(&iter.+1);
	%end;
%mend;
%ext_1;

 

The only thing I can think of is that you have both local and global macro variables assigned to the same name. Start a new SAS session and re-run the code.

 

Art, CEO, AnalystFinder.com

 

Shmuel
Garnet | Level 18

I have made a test simulation of the code and it should work fine.

Please post your full code including the inner code/macro.

 

Here is the code I run:

data have;
  input num word $;
cards;
1 one
2 two
3 three
4 four
; run;

%macro ext_1;
  %let iter=1;
  
  %do %while(&iter <= 4);
     proc sql;
       select word INTO: w from have where num=&iter;
     quit;
     %put W = &w;
     %let iter = %eval(&iter + 1);
  %end;
%mend ext_1;
%ext_1;

attached is the log after running this code.

Tom
Super User Tom
Super User

Your looping seems way too complex.  If you know how many observations are in your input dataset then just loop over them.

%macro ext_1;
%local iter inputfile ;
%do iter=1 %to &n ;
proc sql noprint; 
select filename into :inputfile trimmed 
  from INP_1
  where rown = &iter.
; 
QUIT;
  %put &=iter &=inputfile;
%end;
%mend ext_1;

Example:

data INP_1 ;
  rown+1;
  input filename $50.;
cards;
file1
file2
;
%let n=2;
%ext_1;
310  %let n=2;
311  %ext_1;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


ITER=1 INPUTFILE=file1
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


ITER=2 INPUTFILE=file2

If you don't know then let SAS tell you.

%macro ext_1;
%local iter inputfile found;
%do %until(not &found);
  %let iter=%eval(&iter+1);
proc sql noprint; 
select filename into :inputfile trimmed 
  from INP_1
  where rown = &iter.
; 
QUIT;
  %let found=&sqlobs;
  %if &found %then %do;
    %put &=iter &=inputfile;
  %end;
%end;
%mend ext_1;

 

FP12
Obsidian | Level 7

Thanks all for your answers,

I finally found the core of the issue. But I don't understand it...

 

At the end of my program I logically call the macro ext_1

 

When I do this

%ext_1;

It's ok.

But I don't do this, I do that:

data _null_;
if "&UN." = "YES" then call execute('%ext_1');

run;

UN is a macro variable. In my case it is "YES" but it can also be "NO"

And when I write this I have the issue I was talking about.

 

 

Any explanation why?

Thanks

Tom
Super User Tom
Super User

When you use a statement like this:

call execute('%mymacro;');

Then the SAS macro processor will run the macro and push onto the execution stack the generated SAS code to run AFTER the data step finishes.  So any processing that depended on macro varaibles that were generated by PROC SQL code that the macro generated will NOT use the macro variable values that the PROC SQL code will generate when it runs in the future, but any values that already existed when the data step caused the macro to run.

 

Wrap the macro call in %nrstr() to prevent this and instead just push the actual macro call onto the stack to run after the data step finishes.

data _null_;
  if "&UN." = "YES" then call execute('%nrstr(%ext_1);');
run;

Plus it will make your SAS log much more readable since the lines with + at the start that show the lines of code that CALL EXECUTE() added to the stack will now just look like:

+ %ext_1;

 

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
  • 10 replies
  • 1105 views
  • 0 likes
  • 5 in conversation