%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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.