topic Re: MACRO DATE MANIPULATION - please help! in SAS Procedures

MACRO DATE MANIPULATION - please help!

Mc14 — Mon, 17 Feb 2014 15:42:35 GMT

All, I have seen and read many resources on date manipulation in SAS format specifically, but they have not helped me with this issue at work. I have tried the Sysevalf and the Intnx feature and neither seem to give me what I am looking for. If anyone can shed some light on this I would be very grateful.

Simply put, I am trying to create a set of macros to use in an SQL step which all feed off of one date versus typing in each date separately. Currently I have the below...

%let Related_Act_Date1 = 201212;

%let Related_Act_Date2 = 201301;

%let Related_Act_Date3 = 201302;

%let Related_Act_Date4 = 201303;

%let Related_Act_Date5 = 201304;

%let Related_Act_Date6 = 201305;

%let Related_Act_Date7 = 201306;

%let Related_Act_Date8 = 201307;

%let Related_Act_Date9 = 201308;

%let Related_Act_Date10 = 201309;

%let Related_Act_Date11 = 201310;

%let Related_Act_Date12 = 201311;

%let Related_Act_Date13 = 201312; /*current month*/

Ideally I would like to just type in the current month and have all the others feed from that versus typing them in monthly. This list is much longer, but I have only copied what is hopefully necessary to get my question across. Hopefully you can see how big of a pain it is to change these every month. I need to keep this format YYYYMM and day is not needed. Can anyone shed some light on this by chance?

Thanks so much!

Re: MACRO DATE MANIPULATION - please help!

PaigeMiller — Mon, 17 Feb 2014 16:28:21 GMT

So if I'm understanding properly ... you want to type in 201312 and get the 13 macro variables as shown? Or type in some other year/month and get the appropriate 13 macro variables for that other year/month?

UNTESTED CODE

%let currentmonth=201312;

data _null_;

currmonth=mdy(substr("&currentmonth",5,2),1,substr("&currentmonth",1,4));

do i=1 to 13;

monthsago=13-i;

datemonthsago=intnx('month',currmonth,-monthsago);

call symputx('Related_Act_Date'||left(i),cats(year(datemonthsago),put(month(datemonthsago),z2.)));

end;

run;

Re: MACRO DATE MANIPULATION - please help!

Tom — Mon, 17 Feb 2014 16:29:05 GMT

%SYFUNC() and INTNX() are what you want. Try this:

%let baseyymm = 201212 ;

%let basedate = "%sysfunc(inputn(&baseyymm.01,yymmdd8.),date9.)"d;

%put basedate=&basedate;

%let offset=1 ;

%let newyymm = %sysfunc(intnx(month,&basedate,&offset),yymmn6.);

%put newyymm=&newyymm;

%let offset=14 ;

%let newyymm = %sysfunc(intnx(month,&basedate,&offset),yymmn6.);

%put newyymm=&newyymm;

Re: MACRO DATE MANIPULATION - please help!

Mc14 — Mon, 17 Feb 2014 16:35:50 GMT

Tom -

Thank you so much for your reply, that is EXACTLY what I needed. From reading many of your other responses, you are nothing short of genius with this stuff! Thanks again so much!

Michael