%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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.