I have a data table as below
I would like to match the same business day (same month), current year vs. previous. I have no idea where to start. I can hard code the comparable business day date of last year but there must be a way to pick the same business day date for Sept 2020
For example, September 2021 and September 2020
here is the partial table and output below
Date | Year | BusinessDay | NoofItems | Volume |
20210901 | 2021 | 1 | 918 | 980221 |
20210902 | 2021 | 2 | 775 | 760314 |
20210903 | 2021 | 3 | 659 | 701345 |
20210904 | 2021 | 4 | 1012 | 0 |
20210906 | 2021 | 5 | 883 | 0 |
20210907 | 2021 | 6 | 899 | 0 |
20210908 | 2021 | 7 | 916 | 0 |
20210909 | 2021 | 8 | 932 | 0 |
20200901 | 2020 | 1 | 1082 | 932456 |
20200902 | 2020 | 2 | 1098 | 914315 |
20200903 | 2020 | 3 | 1115 | 962419 |
20200904 | 2020 | 4 | 1132 | 1062458 |
20200905 | 2020 | 5 | 1148 | 1123489 |
20200907 | 2020 | 6 | 1165 | 1326178 |
20200908 | 2020 | 7 | 1181 | 1464218 |
Desired output as below
Date | Business_Day | MTD_Volume_Sep2021 | MTD_Volume_Sep2020 | YoY_Changed |
20210901 | 1 | 980221 | 932456 | 5% |
20210902 | 2 | 1740535 | 1846771 | -6% |
20210903 | 3 | 2441880 | 2809190 | -13% |
20210904 | 4 | 2441880 | 3871648 | -37% |
20210905 | 5 | 2441880 | 4995137 | -51% |
20210906 | 6 | 2441880 | 6321315 | -61% |
20210907 | 7 | 2441880 | 7785533 | -69% |
Clarify your requirement. The "desired" looks like it doing something with CUMULATIVE totals, not day by day and your description is not clear. Otherwise I see no way to get
20210902 | 2 | 1740535 | 1846771 | -6% |
when matching year and "business day".
If that is indeed the case this may be one way (note providing example data in the form of data step code)
data have; input Date :yymmdd10. Year BusinessDay NoofItems Volume; format date yymmddn8.; datalines; 20210901 2021 1 918 980221 20210902 2021 2 775 760314 20210903 2021 3 659 701345 20210904 2021 4 1012 0 20210906 2021 5 883 0 20210907 2021 6 899 0 20210908 2021 7 916 0 20210909 2021 8 932 0 20200901 2020 1 1082 932456 20200902 2020 2 1098 914315 20200903 2020 3 1115 962419 20200904 2020 4 1132 1062458 20200905 2020 5 1148 1123489 20200907 2020 6 1165 1326178 20200908 2020 7 1181 1464218 ; proc sort data=have; by year businessday; run; data need; set have; by year; retain cumvolume; if first.year then cumvolume=0; cumvolume+volume; run; proc sql; create table want as select a.date, a.BusinessDay,a.cumvolume as cyearvol , b.cumvolume as pyearvol, ((pyearvol - cyearvol)/pyearvol) as Yoychange format=percentn8. from need as a left join need as b on a.year=(b.year+1) and a.BusinessDay=b.BusinessDay ; quit;
I did not make any attempt to name values by years because this sort of algorithm would work for multiple years worth of data so used cyear for current year and pyear for previous year.
If actual order is critical then use an Order by clause in the sql.
Many of us refuse to download attachment files. And we can't program from your screen capture of the data. Please provide (a portion of) your data as SAS data step code (instructions).
Also, how do you define "business day"?
Can this help you?
I use the WEEKWw. format.
Maybe you find the WEEKVw. format or the WEEKUw. format more appropriate?
Also, business day is a "elastic" concept.
Easter will "shift" from one year to another for example.
data have20;
do datum20 = '01JAN2020'd to '31DEC2020'd;
datum_weeknumber20 = put(datum20,WEEKW10.);
weekday = substr(datum_weeknumber20,5);
output;
end;
format datum20 date9.;
run;
data have21;
do datum21 = '01JAN2021'd to '31DEC2021'd;
datum_weeknumber21 = put(datum21,WEEKW10.);
weekday = substr(datum_weeknumber21,5);
output;
end;
format datum21 date9.;
run;
data have_20_21;
merge have20
have21;
by weekday;
run;
/* end of program */
Koen
Clarify your requirement. The "desired" looks like it doing something with CUMULATIVE totals, not day by day and your description is not clear. Otherwise I see no way to get
20210902 | 2 | 1740535 | 1846771 | -6% |
when matching year and "business day".
If that is indeed the case this may be one way (note providing example data in the form of data step code)
data have; input Date :yymmdd10. Year BusinessDay NoofItems Volume; format date yymmddn8.; datalines; 20210901 2021 1 918 980221 20210902 2021 2 775 760314 20210903 2021 3 659 701345 20210904 2021 4 1012 0 20210906 2021 5 883 0 20210907 2021 6 899 0 20210908 2021 7 916 0 20210909 2021 8 932 0 20200901 2020 1 1082 932456 20200902 2020 2 1098 914315 20200903 2020 3 1115 962419 20200904 2020 4 1132 1062458 20200905 2020 5 1148 1123489 20200907 2020 6 1165 1326178 20200908 2020 7 1181 1464218 ; proc sort data=have; by year businessday; run; data need; set have; by year; retain cumvolume; if first.year then cumvolume=0; cumvolume+volume; run; proc sql; create table want as select a.date, a.BusinessDay,a.cumvolume as cyearvol , b.cumvolume as pyearvol, ((pyearvol - cyearvol)/pyearvol) as Yoychange format=percentn8. from need as a left join need as b on a.year=(b.year+1) and a.BusinessDay=b.BusinessDay ; quit;
I did not make any attempt to name values by years because this sort of algorithm would work for multiple years worth of data so used cyear for current year and pyear for previous year.
If actual order is critical then use an Order by clause in the sql.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.