BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AshPatel
Fluorite | Level 6

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

  • 20210901 (business day 1) match with 20200901
  • 20210906 (business day 5) match with 20200905
  • 20210907 (business day 6) match with 20200906

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%
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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"?

--
Paige Miller
sbxkoenk
SAS Super FREQ

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

ballardw
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 845 views
  • 1 like
  • 4 in conversation