DATA Step, Macro, Functions and more

Hi I am new to SAS have to call a macro using different values of date

Reply
N/A
Posts: 1

Hi I am new to SAS have to call a macro using different values of date

%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.

Super User
Posts: 19,815

Re: Hi I am new to SAS have to call a macro using different values of date

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.

Super User
Super User
Posts: 7,050

Re: Hi I am new to SAS have to call a macro using different values of date

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

Ask a Question
Discussion stats
  • 2 replies
  • 279 views
  • 0 likes
  • 3 in conversation