topic Re: PROC SQL INTO issue in SAS Programming

PROC SQL INTO issue

FP12 — Wed, 19 Jul 2017 15:42:23 GMT

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

When I run the macro:

%ext_1;

I get a visual resulkt which is ok:

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

Re: PROC SQL INTO issue

kiranv_ — Wed, 19 Jul 2017 16:00:04 GMT

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.

Re: PROC SQL INTO issue

art297 — Wed, 19 Jul 2017 16:01:23 GMT

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

Re: PROC SQL INTO issue

FP12 — Wed, 19 Jul 2017 16:07:11 GMT

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,

Re: PROC SQL INTO issue

art297 — Wed, 19 Jul 2017 16:16:26 GMT

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

Re: PROC SQL INTO issue

FP12 — Wed, 19 Jul 2017 16:19:19 GMT

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

Re: PROC SQL INTO issue

art297 — Wed, 19 Jul 2017 16:36:13 GMT

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

Re: PROC SQL INTO issue

Shmuel — Wed, 19 Jul 2017 16:40:02 GMT

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.

Re: PROC SQL INTO issue

Tom — Wed, 19 Jul 2017 18:23:03 GMT

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;

Re: PROC SQL INTO issue

FP12 — Fri, 21 Jul 2017 14:03:08 GMT

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

Re: PROC SQL INTO issue

Tom — Fri, 21 Jul 2017 14:58:04 GMT

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;