topic Re: Dynamic coding solution for Year over Year Comparisons of a daily variable like Sales, Claims in SAS Programming

Dynamic coding solution for Year over Year Comparisons of a daily variable like Sales, Claims

steveocaffey — Thu, 02 Jun 2022 21:19:24 GMT

I am hoping some of you smart folks can help me with a dynamic coding solution that is not hard coded. My problem is that I am trying to a monthly comparison of sales year over year. The underlying data has data reported Monday through Friday. The inherent problem is that calendar weeks are different each year where part of the first or last week spans into the following month.

The end-goal is to align the same number of working days for any given month. Currently I am using a hard-coded solution that increments the actual month based by +1 or -1 based on the actual date. A couple of examples; (1) 05/30/21, 05/30/22 and 05/31/22 are hard coded so the month reflects June or ‘06’ (2) 04/01/21, 04/02/21 and 02/28/22, 04/01/22 are hard coded so the month reflects March or ‘03’.

I have been trying to use the new retail dates ‘R445MON’ coupled with the INTNX function, but I don’t believe this is a solution. I am not able to get the same result as my hard coded solution. I have also search for end-users with a similar problem to no avail.

Thank you in-advance for any help.

Re: Dynamic coding solution for Year over Year Comparisons of a daily variable like Sales, Claims

ballardw — Thu, 02 Jun 2022 21:59:51 GMT

Some example data and result of your "hard coding" might be helpful.

You say "monthly" and then get into "weeks" without quite covering in a bit of detail what your rules are. What kind of "month" comparison are you looking to do? Total? Mean daily? something else?

Re: Dynamic coding solution for Year over Year Comparisons of a daily variable like Sales, Claims

steveocaffey — Fri, 03 Jun 2022 18:50:27 GMT

Apologies if this posts twice.

Thank you for your response and please accept my apologies for any confusion. Please let me provide a little more background around the problem.

Basically I am creating a simple monthly report showing retail sales for 2021 and 2022 (all calendar based). The problem is that simply comparing the month to a prior year can sometimes be skewed say if Mar-22 has 5 Saturdays vs. 4 Saturday’s in Mar-21. Retail businesses deal with this problem where working days can be different for the same month in the current and prior year.

Currently my solution is not very elegant, and I was curious if there is a dynamic solution. Starting with the 1st Monday of the year, I reclassify the month based on the actual date where needed so there is a consistent number of working days and thus weeks when looking at any given month. The sample code belows shows how I handle this in May-21 and May-22. There is no data reported for weekends (Sat – Sun reported on Monday).

Sample Code:

DATA GRAPEAPE;
INPUT DATE1 MMDDYY10.
PMON
PYEAR
SALES
;
FORMAT DATE1 MMDDYY10.
PMON Z2.;
DATALINES;
05 27 2021 05 2021 1000
05 28 2021 05 2021 2000
05 31 2021 05 2021 3000
06 01 2021 06 2021 1100
06 02 2021 06 2021 1200
06 03 2021 06 2021 1300
06 04 2021 06 2021 1400
05 27 2022 05 2022 1111
05 30 2022 05 2022 1111
05 31 2022 05 2022 1133
06 01 2022 06 2022 1155
06 02 2022 06 2022 1177
06 03 2022 06 2022 2311
06 04 2022 06 2022 2322
;
RUN;

PROC PRINT DATA = GRAPEAPE;
TITLE 'DAILY SALES';
RUN;

DATA SPEEDRACER;
SET GRAPEAPE;

ATTRIB NEWMON FORMAT=Z2.;
NEWMON=PMON;
IF DATE1 IN('31MAY2021'D) THEN NEWMON=6;
ELSE IF DATE1 IN('30MAY2022'D,'31MAY2022'D) THEN NEWMON=6;

RUN;

PROC PRINT DATA = SPEEDRACER;
TITLE 'DAILY SALES';
RUN;

TITLE1 'SALES BY MONTH';
PROC SQL;

SELECT PYEAR
,PMON
,SUM(SALES) AS TOTSALES
FROM SPEEDRACER
GROUP BY PYEAR, PMON;

SELECT PYEAR
,NEWMON
,SUM(SALES) AS TOTSALES
FROM SPEEDRACER
GROUP BY PYEAR, NEWMON;
QUIT;
RUN;