CODEWITHSUNDEEP
sassas-macro
Franz Stoneking
Franz Stoneking

Reputation: 35

SAS macro variable issue- 'File Name value exceeds maximum length'

I'm trying to leverage macro variables (assigned via %LET) for PROC IMPORTS but it's causing the file name to exceed 201 characters. (ERROR: File Name value exceeds maximum length of 201 characters)

Is there a different way to define variables so that they are passed as values instead of functions?

My old datafile string ends DATAFILE="...Files_Submitted\201612\Reconcile\Q42016 Principal balances.xls"

I have changed it to DATAFILE="...Files_Submitted\&yrmm.\Reconcile\&quarter. Principal balances.xls"

Using the following variables.

%LET EOLM= INTNX('MONTH',today(),-&MonthsAgo.);
%LET yrmm= COMPRESS(year(&EOLM.)||PUT(month(&EOLM.),z2.));
%LET qtr = COMPRESS(year(&EOLM.)||COMPRESS('Q'||CEIL(month(&EOLM.)/3)));

Thanks in advance for any/all assistance.

Upvotes: 1

Views: 3988

Answers (1)

Joe
Joe

Reputation: 63424

You're not actually passing things the right way here - you're mixing two different things, which is causing your issue.

You can either resolve the results of these functions in your macro variables, OR you can store the function calls and treat this as if you'd typed the functions into the data step - so use CATS or something to combine them.

As it is, you're ending up with a filename like "\\path\to\COMPRESS(INTNX(MONTH...", not with the results of those functions, hence your problem.

So, one option:

DATAFILE= cats("...Files_Submitted\",&yrmm.,"\Reconcile\",&quarter.,"Principal balances.xls";

That would let the functions provide their values as you expect.

The other option is to use %SYSFUNC to ask the values to be resolved in the macro variables. This is the more common way, though certainly neither is really specifically better for all purposes.

%LET EOLM= %sysfunc(INTNX(MONTH,%sysfunc(today()),-&MonthsAgo.));

And similar for the other two. Note that I remove the quotes around MONTH as quotes are not used in %SYSFUNC calls (unless you want to use quote characters themselves, but not as string delimiters). 

%LET yrmm= %sysfunc(year(&EOLM.))%sysfunc(month(&EOLM.),z2.);

Note here I put the format in the SYSFUNC call directly; also note that we do not use concatenation characters in macro variables (they just produce text) and typically you don't need to use COMPRESS (though not always).

%LET qtr = %sysfunc(year(&EOLM.))Q%sysfunc(CEIL(%sysevalf(%sysfunc(month(&EOLM.))/3)));

Here we use %SYSEVALF to do the math (normally you can't have noninteger math in macro syntax). We also remove quotes from Q and just place it in line.

Putting it all together:

   %let monthsAgo = 3;
   %LET EOLM= %sysfunc(INTNX(MONTH,%sysfunc(today()),-&MonthsAgo.));
   %put &=EOLM;
EOLM=20789
   %LET yrmm= %sysfunc(year(&EOLM.))%sysfunc(month(&EOLM.),z2.);
   %put &=yrmm;
YRMM=201612
   %LET qtr =   %sysfunc(year(&EOLM.))Q%sysfunc(CEIL(%sysevalf(%sysfunc(month(&EOLM.))/3)));
   %put &=qtr;
QTR=2016Q4

Of course more easy might have been to use formats for yrmm/qtr...

%let yrmm = %sysfunc(putn(&eolm.,yymmn6.));
%let qtr  = %sysfunc(putn(&eolm.,yyq6.));
%put &=yrmm &=qtr;

Or even (and this might be getting a bit cute) removing the %SYSFUNC from &EOLM and letting the %SYSFUNC format option handle the formatting. Note here EOLM does not store a number, but stores the text you see on the screen, and the number doesn't get resolved until YRMM or QTR is defined.

%LET EOLM= INTNX(MONTH,%sysfunc(today()),-&MonthsAgo.);
%let yrmm = %sysfunc(&eolm.,yymmn6.);
%let qtr  = %sysfunc(&eolm.,yyq6.);
%put &=yrmm &=qtr;

Upvotes: 3

Related Questions