DATA Step, Macro, Functions and more

PROC SQL INTO issue

Reply
Contributor
Posts: 34

PROC SQL INTO issue

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

PROC Star
Posts: 326

Re: PROC SQL INTO issue

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.

PROC Star
Posts: 7,487

Re: PROC SQL INTO issue

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

Contributor
Posts: 34

Re: PROC SQL INTO issue

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,

 

PROC Star
Posts: 7,487

Re: PROC SQL INTO issue

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

 

Contributor
Posts: 34

Re: PROC SQL INTO issue

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

PROC Star
Posts: 7,487

Re: PROC SQL INTO issue

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

 

Trusted Advisor
Posts: 1,583

Re: PROC SQL INTO issue

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.

Super User
Super User
Posts: 7,074

Re: PROC SQL INTO issue

[ Edited ]

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;

 

Contributor
Posts: 34

Re: PROC SQL INTO issue

[ Edited ]

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

Super User
Super User
Posts: 7,074

Re: PROC SQL INTO issue

[ Edited ]

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;

 

Ask a Question
Discussion stats
  • 10 replies
  • 200 views
  • 0 likes
  • 5 in conversation