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

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

ashwary — Thu, 27 Feb 2014 20:12:20 GMT

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

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

Reeza — Thu, 27 Feb 2014 20:28:43 GMT

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.

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

Tom — Sun, 09 Mar 2014 02:28:24 GMT

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