Hi folks, this is long, so please forgive, but I'd like to understand best practice around working with date calcs.
We do some intensive querying with SAS and I'm going through and automating some process flows. One of the inconveniences I'm immediatly trying to solve is needing to input numerous specific dates in order to retrieve a desired result when querying. My general appoach to fixing this is to have the user input one or two dates, and create the rest of the desired dates via date-calculation.
I approach date calculation two ways... if the user is unputing "dates" as character strings (e.g. "2015-01-01") Then I substring the string, into yr/mo/day, convert to numeric values, CATT, and date format. I then do some math off of the constituant parts of the character date they are providing (shown below)
DATA Tag_Test;
/* Method One */ /* Specified Date Rage as Character Strings */ Start_D = '01/04/2015'; End_D = '02/12/2015';
Start_Yr = INPUT(SUBSTR(Start_D,7,4),9.); Start_Mo = INPUT(SUBSTR(Start_D,1,2),9.); Start_Dy = INPUT(SUBSTR(Start_D,4,2),9.); End_Yr = INPUT(SUBSTR(End_D,7,4),9.); End_Mo = INPUT(SUBSTR(End_D,1,2),9.); End_Dy = INPUT(SUBSTR(End_D,4,2),9.); LY = Start_Yr - 1;
Start_Date = INPUT(CATT(Start_Mo,'/',Start_Dy,'/',Start_Yr),MMDDYY10.); FORMAT Start_Date Date9.; End_Date = INPUT(CATT(End_Mo,'/',End_Dy,'/',End_Yr),MMDDYY10.); FORMAT End_Date Date9.; LY_Start = INPUT(CATT('01','/','01','/',LY),MMDDYY10.); FORMAT LY_Start Date9.; LY_End = INPUT(CATT('12','/','31','/',LY),MMDDYY10.); FORMAT LY_End Date9.; TY_Start = INPUT(CATT('01','/','01','/',Start_Yr),MMDDYY10.); FORMAT TY_Start Date9.; TY_Day2 = INPUT(CATT('01','/','02','/',Start_Yr),MMDDYY10.); FORMAT TY_Day2 Date9.; TY_Start_Less_1 = INTNX('day',Start_Date,-1); FORMAT TY_Start_Less_1 Date9.;
RUN;
If the user is providing numeric dates within SAS I go with standard DATE functions & INTNX to arrive at the dates needed.
DATA Tag_Test;
/* Method Two */ /* Specified Date Rage from Date Values */
Start_Date = '01FEB2015'd; FORMAT Start_Date Date9.; End_Date = '03FEB2015'd; FORMAT End_Date Date9.;
Start_Yr = Year(Start_Date); Start_Mo = Month(Start_Date); Start_Dy = Day(Start_Date); End_Yr = Year(End_Date); End_Mo = Month(End_Date); End_Dy = Day(End_Date); LY = Start_Yr -1;
LY_Start = INPUT(CATT(01,'/',01,'/',LY),MMDDYY10.); FORMAT LY_Start Date9.; LY_End = INPUT(CATT(12,'/',31,'/',LY),MMDDYY10.); FORMAT LY_End Date9.; TY_Start = INPUT(CATT(01,'/',01,'/',Start_Yr),MMDDYY10.); FORMAT TY_Start Date9.; TY_Day2 = INPUT(CATT(01,'/',02,'/',Start_Yr),MMDDYY10.); FORMAT TY_Day2 Date9.; TY_Start_Less_1 = INTNX('day',Start_Date,-1); FORMAT TY_Start_Less_1 Date9.;
RUN;
However, then I try to create global macro's to be used in several queries, I run into issues. Mainly, how to do date calculations to create macro variables when I'm not creating variables in a table. The code works fine when SAS understands I'm creating table output. But when I try to code without specifying DATA "table_name"; ......RUN; it does not recognize my date variables as anything but strings. If I were to run the below snippet, and %put LY, it sees LY = "Start_Yr -1", and not the date output.
%Let Start_Date = '01FEB2015'd; %Let End_Date = '03FEB2015'd;
%Let Start_Yr = Year(Start_Date); %Let Start_Mo = Month(Start_Date); %Let Start_Dy = Day(Start_Date); %Let End_Yr = Year(End_Date); %Let End_Mo = Month(End_Date); %Let End_Dy = Day(End_Date); %Let LY = Start_Yr -1;
%Let LY_Start = INPUT(CATT(01,'/',01,'/',LY),MMDDYY10.); %Let LY_End = INPUT(CATT(12,'/',31,'/',LY),MMDDYY10.); %Let TY_Start = INPUT(CATT(01,'/',01,'/',Start_Yr),MMDDYY10.); %Let TY_Day2 = INPUT(CATT(01,'/',02,'/',Start_Yr),MMDDYY10.); %Let TY_Start_Less_1 = INTNX('day',Start_Date,-1);
%put &Start_Yr;
... View more
