CODEWITHSUNDEEP
sas
sharkxii
sharkxii

Reputation: 13

Using a case function in macro for date

I'm trying to create a table that always shows the last Friday of last month using a case function but I'm having trouble with the code.

%let eomb=%sysfunc(Case 
when %sysfunc(weekday(intnx(month, "&sysdate", -1, end))=1) then %sysfunc(intnx(month, "&sysdate", -1, end)-2)
when %sysfunc(weekday(intnx(month, "&sysdate", -1, end))=7) then %sysfunc(intnx(month, "&sysdate", -1, end)-1)
else %sysfunc(intnx(month, "&sysdate", -1, end))
end);

%put &eomb.;


proc sql;

create table SNAP_DT
    (SNAP_DT DATE FORMAT = date9.
                  INFORMAT = date9.);

INSERT INTO SNAP_DT
    Values (&eomb);

    run;

Upvotes: 1

Views: 219

Answers (1)

Reeza
Reeza

Reputation: 21294

You cannot use CASE in a macro function like that. You can use IFN or IFC though. Though you can get the same thing by using INTNX. Here's one approach.

  1. Use DATA null to avoid issues with macro functions
  2. Use NWKDOM - which finds the nth day of the month/year. You do have to know the month/year so need to calculate that first.

  3. use CALL SYMPUTX to create macro variable

    data _null_;
prev_month = intnx('month', today(), -1, 'b');
x=nwkdom(5, 6, month(prev_month), year(prev_month));
call symputx('eomb', x, 'g');
run;

%put &eomb.;

You can nest this all into macro functions but that's a lot of %SYSFUNC() that is annoying to debug. 

%let eomb2 = %sysfunc(nwkdom(5, 
                            6, 
                            %sysfunc(month(%sysfunc(intnx(month, %sysfunc(today()), -1, b)))), 
                            %sysfunc(year(%sysfunc(intnx(month, %sysfunc(today()), -1, b))))
                             )
                        );
%put &eomb2;

Upvotes: 1

Related Questions