PROC SQL INTO issue

FP12 — Wed, 19 Jul 2017 15:22:13 GMT

I have a very strange functional issue.

I have a table of 37 rows, 2 colums:

filename	ROWN
P01ADIBP_10188_201610050135_A00000AY.txt	1
P01ADIBP_10206_201610050230_A00000C3.txt	2
P01ADIBP_11006_201610050225_A00000C2.txt	3
P01ADIBP_11206_201610050214_A00000BJ.txt	4
P01ADIBP_11306_201610050208_A00000BA.txt	5
P01ADIBP_11706_201610050232_A00000C8.txt	6
P01ADIBP_12006_201610050139_A00000B0.txt	7
P01ADIBP_12206_201610050209_A00000BC.txt	8
P01ADIBP_12406_201610050204_A00000B5.txt	9
P01ADIBP_12506_201610050217_A00000BS.txt	10
P01ADIBP_12906_201610050210_A00000BE.txt	11
P01ADIBP_13106_201610050210_A00000BD.txt	12
P01ADIBP_13306_201610050218_A00000BQ.txt	13
P01ADIBP_13506_201610050217_A00000BO.txt	14
P01ADIBP_13606_201610050213_A00000BH.txt	15
P01ADIBP_14006_201610050732_A00000DD.txt	16
P01ADIBP_14406_201610050215_A00000BL.txt	17
P01ADIBP_14506_201610050212_A00000BF.txt	18
P01ADIBP_14706_201610050216_A00000BR.txt	19
P01ADIBP_16006_201610050220_A00000BT.txt	20
P01ADIBP_16106_201610050207_A00000B8.txt	21
P01ADIBP_16606_201610050212_A00000BG.txt	22
P01ADIBP_16706_201610050215_A00000BN.txt	23
P01ADIBP_16806_201610050234_A00000CC.txt	24
P01ADIBP_16906_201610050235_A00000CE.txt	25
P01ADIBP_17106_201610050150_A00000B2.txt	26
P01ADIBP_17206_201610050225_A00000BZ.txt	27
P01ADIBP_17806_201610050350_A00000CW.txt	28
P01ADIBP_17906_201610050216_A00000BP.txt	29
P01ADIBP_18106_201610050157_A00000B3.txt	30
P01ADIBP_18206_201610050328_A00000CU.txt	31
P01ADIBP_18306_201610050209_A00000BB.txt	32
P01ADIBP_19106_201610050223_A00000BY.txt	33
P01ADIBP_19406_201610050205_A00000B6.txt	34
P01ADIBP_19506_201610050207_A00000B9.txt	35
P01ADIBP_19806_201610050730_A00000DC.txt	36
P01ADIBP_19906_201610042343_A00000AW.txt	37

First the filename, second the rownumber.

The table is called INP_1.

Now 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;

My macrovariable nbfile = 37.

When I run that I have a visual result

This is ok.

However in the log...following is an extract (from iter = 4 to 6, it is the same before and after):

[...]
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 equals to:

P01ADIBP_19906_201610042343_A00000AW.txt

Which is the last filename of INP_1

Do you know what's wrong here?

Thanks

Re: PROC SQL INTO issue

Astounding — Wed, 19 Jul 2017 15:20:54 GMT

You chopped off the macro definition a little early here. By any chance are you missing a semicolon at the end of the %PUT statement?

Re: PROC SQL INTO issue

FP12 — Wed, 19 Jul 2017 15:24:06 GMT

Indeed,

I just edited my post.

No semicolon missing I think (otherwise it shouldn't run no?)

topic Re: PROC SQL INTO issue in SAS Programming

PROC SQL INTO issue

Re: PROC SQL INTO issue

Re: PROC SQL INTO issue