%macro yer();
data DATE_LKUP;
MON= month(today());
YR= YEAR(today());
IF MON eq 1 then MONTH= 12;
else MONTH = MON -1;
IF MON eq 1
then do;
Year = YR - 1 ;
end;
Else
do;
Year = YR ;
end;
If MON eq 1 or MON eq 2 or MON eq 3 then YearQuarter= Year || '-Q1';
Else If MON eq 4 or MON eq 5 or MON eq 6 then YearQuarter= Year || '-Q2';
ELSE IF MON eq 7 or MON eq 8 or MON eq 9 then YearQuarter= Year || '-Q3';
ELSE IF MON eq 10 or MON eq 11 or MON eq 12 then YearQuarter= Year || '-Q4';
Year1= Year -1;
Year2= Year -2;
ReportDATE= Month||'-'||Year;
%let Year = Year;
%let Year2 = Year2;
%let Year1 = Year1;
%let Month = Month;
%let YearQrt = YearQuarter;
%let ReportDATE = ReportDATE;
run;
%SnapShotDate(&Year,&Month,&YearQrt,3);
%SnapShotDate(&Year1,&Month,&YearQrt,2);
%SnapShotDate(&Year2,&Month,&YearQrt,1);
%BuildExtractFile(&ReportDATE);
%mend yer;
When I am running this code I am getting below error
ERROR: CLI describe error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid
column name 'Month'. : [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid
column name 'Year'. : [Microsoft][SQL Server Native Client 10.0][SQL
Server]Statement(s) could not be prepared.
Use call symputx() to create a macro variable in a data step.
You may also want to look into some date formats such as YYqq format.
Looks like the macro %SNAPSHOTDATE() is calling SQL server.
How does this macro expect its parameters formatted?
You do not need the macro %YER() to generate the dates to call the other macro. Either just do the formatting in a data step or even in open code using %SYFUNC() calls. It is not clear what you want to do but if you want to find the previous month or quarter you can use the INTNX() function with a negative offset.
%let today=%sysfunc(today());
%let lastmonth=%sysfunc(intnx(month,&today,-1));
If you want the date in a particular format then use a format on the %SYSFUNC() call.
%let lastqtr = %sysfunc(intnx(month,&today,-1),YYQ6.);
LASTQTR = 2014Q1
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.