How do I read data using a concatenated string filename?

[CalebSturges]

I'm trying to make my SAS program more parameterizable. It is pullng data and writing data on a Linux SAS Server (9.3) being called by a SAS Enterprise Guide's (5.1) code on a Windows PC being called by an Excel VBA command prompt, but the inter program communication is working fine. It requires me to pass dates to be used as file names. These need to be used to access ".sas7bdat" files in a read only section of the Linux server and run a model, but its probably not that important unless it needs a very complex workaround.

The original code runs fine, but the new code which sets the filename with a concatenated string runs into an error:

libname out '/opt/local/sasuser/';
libname cf  '/opt/local/portfolio/';

*INPUT;
%LET START  = '31DEC2016'D; 
%LET CFDATAF = CAT("server_", "201701");
%LET CFDATA = cf.CFDATAF; *Old Code just read: %LET CFDATA = cf.server_201701; 

*Cleaning the bureau data;
data cf; 
set &CFDATA.;
*Merging Key;
FORMAT DATA_AS_OF_DT DATE9.;
DATA_AS_OF_DT = &START.;

A1_n = A1*1;LABEL A1_n = 'IO1';
A2_n = A2*1;LABEL A2_n = 'IO2';

KEEP ACCT_DIM_NB DATA_AS_OF_DT
A1_n A2_n
;RUN;

32         set &CFDATA.;
ERROR: File CF.CFDATAF.DATA does not exist.

I've tried: changing it so the concatenated strings are directly set to CFDATA, passing data the whole path, reconfiguring the data step using several different methods, changing the working directory and passing just the concatenated file name (unable to change the directory or move files with simple Unix commands probably don't have enough user rights) and copying the files to the working directory to directly access them with string filename.

Also, I havn't added the part where "201701" is a date sent by VBA using "SYSPARM" because it would introduce complication, but I'm open to a VBA solution.

I've looked on SAS's documentation of its data step and libref, they do say several times that "strings will not work." But what's the workaround?

[ballardw]

1) I think you meant

set &CFDATAF.;

2) YOu should look at the value generated for CFDATAF: %put &cfdataf.;

3) Try again  with

%LET CFDATAF = %sysfunc(CATTserver_, 201701));

[CalebSturges]

Q. 1) I think you meant

set &CFDATAF.;

A. 1)

I meant &CFDATA, because thats what made the old code run but I will try it with and without the "F" from now on. (didn't work first time)

Q. 2) YOu should look at the value generated for CFDATAF: %put &cfdataf.;

A. 2) SAS output:

24         %Put &CBDATAF;
CAT('server_', '201701')

3) Try again  with

%LET CFDATAF = %sysfunc(CATTserver_, 201701));

That code doesn't run Because its missing the first parenthasis.

But if you meant: %LET CFDATAF = %sysfunc(CAT(server_, 201701));

201701 is not  a string so it doesn't help me because I am trying to do use strings

but it also gave this error:

36         set &CBDATAF.;

ERROR: File WORK.SERVER_201701.DATA does not exist.

Or if you meant: %LET CFDATAF = %sysfunc(CAT("server_", "201701"));

36         set &CBDATAF.;
ERROR: File server_'201701 does not exist.

[CalebSturges]

Q. 1) I think you meant

set &CFDATAF.;

A. 1)

I meant &CFDATA, because thats what made the old code run but I will try it with and without the "F" from now on. (didn't work first time)

Q. 2) YOu should look at the value generated for CFDATAF: %put &cfdataf.;

A. 2) SAS output:

24         %Put &CBDATAF;
CAT('server_', '201701')

3) Try again  with

%LET CFDATAF = %sysfunc(CATTserver_, 201701));

That code doesn't run Because its missing the first parenthasis.

But if you meant: %LET CFDATAF = %sysfunc(CAT(server_, 201701));

201701 is not  a string so it doesn't help me because I am trying to do use strings

but it also gave this error:

36         set &CBDATAF.;

ERROR: File WORK.SERVER_201701.DATA does not exist.

Or if you meant: %LET CFDATAF = %sysfunc(CAT("server_", "201701"));

36         set &CBDATAF.;
ERROR: File server_'201701 does not exist.

[Reeza]

It needs to resolve to valid SAS code.

This is probably what you were trying to do. Always start with SAS code that works and then convert it to a macro step by step.

Based on comments you seemed to be missing the library reference.

I automated the calculation of the month portion of the dataset name. 

CATT functions don't work well in macros since they automatically convert numeric data, but you also don't really need them. 

If something doesn't work, include the full code and log.

libname out '/opt/local/sasuser/';
libname cf  '/opt/local/portfolio/';

*INPUT;
%LET START  = '31DEC2016'D; 
%let d_month = %sysfunc(intnx(month, &start, 1, b), yymmn6.);
%put &d_month;
%LET data_set_name = server_&d_month.;
%put &data_set_name;
 

*Cleaning the bureau data;
data cf; 
set cf.&data_set_name.;
*Merging Key;
FORMAT DATA_AS_OF_DT DATE9.;
DATA_AS_OF_DT = &START.;

A1_n = A1*1;LABEL A1_n = 'IO1';
A2_n = A2*1;LABEL A2_n = 'IO2';

KEEP ACCT_DIM_NB DATA_AS_OF_DT
A1_n A2_n;
RUN;

[CalebSturges]

This one works! But it relies on the increment trick, it might not work for some variables that aren't dates or if the File name isn't one day plus the StartDate. Also it START has to be pushed through the command prompt, so its just kicking the can down the road to some extent.

But I think your DEQUOTE("File") is the most general solution. I need to double check it with my VBA code.

[Astounding]

It's not 100% clear what parameters you would like to be able to use.  But look at how macro language handles strings and how it handles dots to delimit macro variable names.  If you were to start with this:

%let libref=crf;

%let prefix=server_;

%let month = 201701;

With those three macro variables, you could put them together easily:

set &libref..&prefix_.&month;

[CalebSturges]

You can use any parameters (including from the command prompt) you want except that '201701' must be a passed on quotes  as a string. I will be passing it as astring through the command line.

I assume you ment "cf" crf just gives an error.

your code did not have 201701 as string literal so it wouldn't fit my puropose. But when I ran it it gave an error:

38         data cf;
WARNING: Apparent symbolic reference PREFIX_ not resolved.
39         set &libref..&prefix_.&month; *CFDATA.;
NOTE: Line generated by the macro variable "LIBREF".
2                                                          The SAS System                              09:31 Tuesday, March 14, 2017

39          cb
            ___
            22
            201
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END, INDSNAME, KEY, KEYS, NOBS, OPEN, 
              POINT, _DATA_, _LAST_, _NULL_.  

ERROR 201-322: The option is not recognized and will be ignored.

I also tried to use string literals for both. i.e.

%let prefix="server_";
%let month = "201701";

38         data cf;
WARNING: Apparent symbolic reference PREFIX_ not resolved.
39         set &libref..&prefix_.&month; *CFDATA.;
NOTE: Line generated by the macro variable "LIBREF".
39          cb
2                                                          The SAS System                              09:31 Tuesday, March 14, 2017

            ___
            22
            201
ERROR: File 201701 does not exist.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END, INDSNAME, KEY, KEYS, NOBS, OPEN, 
              POINT, _DATA_, _LAST_, _NULL_.  

ERROR 201-322: The option is not recognized and will be ignored.

[Reeza · Accepted solution]

---

@CalebSturges wrote:

You can use any parameters (including from the command prompt) you want except that '201701' must be a passed on quotes  as a string. I will be passing it as astring through the command line.

---

Since SAS macro variables need to not have quotes in your particular usuage then you need to remove the quotes if you're passing them through the command line. There's a function for that, it's called DEQUOTE().

[CalebSturges]

Ya, using %sysfunc(DEQUOTE("FilePart")) is the best solution.

so just replace line 6 in the original with:

%LET CFDATA = cf.server_%sysfunc(DEQUOTE("201701"));  

Thanks for your time,

[Astounding]

At least part of that is my fault.  With these statements:

%let libref=crf;

%let prefix=server_;

%let month = 201701;

The correct usage would be:

set &libref..&prefix.&month;

The value of &PREFIX includes the underscore, and the name of the macro variable is &PREFIX (not &PREFIX_).

[CalebSturges]

This one works if you change  crf to cf. But it uses the unquoted version of 201701, so it doesn't answer my Q.