topic Re: Proc SQL in New SAS User

SQL: match record with previous year on same business day

AshPatel — Mon, 06 Dec 2021 07:04:41 GMT

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%

Re: Proc SQL

PaigeMiller — Sun, 05 Dec 2021 21:09:25 GMT

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

Re: Proc SQL

sbxkoenk — Sun, 05 Dec 2021 21:22:36 GMT

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

Re: Proc SQL

ballardw — Sun, 05 Dec 2021 22:58:24 GMT

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

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.