BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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

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
  • 2 replies
  • 1327 views
  • 0 likes
  • 2 in conversation