use sql to get macro variable as filename

deleted_user — Tue, 27 Jan 2009 05:07:19 GMT

i use sql to get a macro variable as the name of a file, then want to read this file, but found problems

two programs are as follows; is there any diffence btw them

first,

PROC IMPORT OUT= WORK.t
DATAFILE= "C:\Users\rong\Documents\high-frequency\2006\20061
1\200611sh_csv\sh\20061101\SH000001.csv"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

second

proc sql noprint;
select code
into: wkbnm
from jj
where row=1;

%put &wkbnm;

PROC IMPORT OUT= WORK.t
DATAFILE= "C:\Users\rong\Documents\high-frequency\2006\20061
1\200611sh_csv\sh\20061101\&wkbnm..csv"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

jj is as follows
code row
SH000001 1
SH000002 2
SH000003 3

Re: use sql to get macro variable as filename

Cynthia_sas — Tue, 27 Jan 2009 05:34:51 GMT

Hi:
You could try turning on the SYMBOLGEN option to see how &wkbnm is being resolved. Depending on the length of &wkbnm in the work.jj dataset, you could be generating a name that looks like this after macro variable resolution:
[pre]
SH000001 .csv (with trailing spaces)
instead of this:
SH000001.csv (without trailing spaces)
[/pre]

Trailing spaces or blanks are not generally stripped from macro variables when you create them with PROC SQL. SYMBOLGEN can help you figure out what's happening. You can also figure out whether this is what's happening in your case with a simple test. Create this new &FNAME macro variables AFTER your PROC SQL step (note that the PROC SQL step ends with a QUIT and not with a RUN). If you want your macro variables available after the step runs, then you must have a "good" step boundary to the SQL step.
[pre]
proc sql noprint;
select code
into: wkbnm
from jj
where row=1;
quit;

%let fname = &wkbnm..csv;
%put &fname;
[/pre]

If &FNAME displays as
[pre]SH000001 .csv[/pre]

then you know that you need to trim the trailing blanks from the macro variable value. (even one space before the ".csv" would result in an incorrect filename reference for your PROC IMPORT syntax.

Trimming the trailing spaces can be done VERY easily, by issuing a %let after the QUIT; statement for the SQL step:

[pre]
proc sql noprint;
select code
into: wkbnm
from jj
where row=1;
quit;

%let wkbnm = &wkbnm;
%let fname = &wkbnm..csv;
%put &fname;
[/pre]

Note the inclusion of the extra %LET statement -- macro variable values assigned with a %LET have the leading and trailing blanks removed from the variable value.

cynthia

Re: use sql to get macro variable as filename

deleted_user — Wed, 28 Jan 2009 01:38:08 GMT

thanks a lot

topic Re: use sql to get macro variable as filename in SAS Programming

use sql to get macro variable as filename

Re: use sql to get macro variable as filename

Re: use sql to get macro variable as filename