topic Re: How to add dynamically weekday column counts in the report as the week progress? in ODS and Base Reporting

How to add dynamically weekday column counts in the report as the week progress?

Stalk — Wed, 10 Feb 2021 17:11:07 GMT

I am working on a report to automatically send daily report to the users with a table in the body of the email. One of the requirement is to have a column for total count, prior week counts and this weeks counts( break down by day).. Not sure how to add the columns as the week progress with their respective header names.

started to get last week dates but not sure how to add the columns in the proc report.. some times there might not be any data for that day but still need to show for the 3 products..

%let fdlw=%sysfunc(intnx(week,%sysfunc(today()),-1,b),date9.);

%put first_day=&fdlw ;

%let ldlw=%sysfunc(intnx(week,%sysfunc(today()),0,b),date9.);

%put last_day=&ldlw ;

PROC REPORT DATA=daily_Counts nowd HEADLINE HEADSKIP SPLIT='*'

style (report) = {background = white

font_face = "Verdana" font_size = 7pt just=left }

style (column) = {background = white CELLHEIGHT = 2.5%

font_face = "Verdana" font_size = 7pt just=left}

style (header) = {foreground = cx5e2750 font_face="Verdana"

font_size = 8pt just=left

background = white} ;

column product Description Total_counts Prior_week Day1 Day2

;

define Product / display width= 15 "Product";

define Description / display width= 30 " Description ";

define Total_counts / display width= 30 " ";

define prior_week / display width= 30 "”;

define Day1 / display width= 30 " ";

run;

Product	Description	Total Products in the system	Total Products in the system Received Prior week( Sunday to Saturday)	Total Products in the system Received Sunday	Total Products in the system Received Monday
1235	Item1	3173	1070	73	34
5432	Item4	340	96	19	2
6574	Item5	5	2	-	3

Re: How to add dynamically weekday column counts in the report as the week progress?

PaigeMiller — Wed, 10 Feb 2021 18:39:26 GMT

It's not clear to me what you are asking.

Do you mean that at a certain day, there are two daily columns and the next day (automatically, without modifying the program) there are three daily columns and the next day (automatically, without modifying the program) there are four daily columns, etc. (in each case with the previous week column)?

Re: How to add dynamically weekday column counts in the report as the week progress?

Stalk — Wed, 10 Feb 2021 19:31:13 GMT

yes, I am planning to run a daily sas job where it automatically sends email to users with a tabular report having the following columns. Product ,Description, Total_counts ,Prior_week . Prior_week is from Sunday to Saturday last week( Jan31 to Feb 6th) for this entire week. I handled all these 4 columns but thinking how to add week days columns dynamically? For example:
In addition to the above 4 columns, if the program runs on Sunday it will have 5 columns, i.e additional column saying "Total Products in the system Received Sunday" .. On Monday there will be 6 columns "Total Products in the system Received Monday" and soon until Saturday. On Saturday the report will have 11 columns(4 basic columns + Sunday to Saturday column counts)
Then The cycle repeats. In the next week's report: Prior_week will have Feb7 to 13 counts and On Sunday it will have 5 columns ( 4 base columns + Sunday) "Total Products in the system Received Sunday".... Sometimes Saturday and Sunday might have 0 counts but still I want to show the columns

Re: How to add dynamically weekday column counts in the report as the week progress?

PaigeMiller — Wed, 10 Feb 2021 19:39:13 GMT

PROC REPORT can add additional columns without additional programming.

IMPORTANT CONCEPT: even though you want the output from PROC REPORT to have more columns each day (until it shrinks at the end of the week to fewer columns), you want your DATA set to have more rows as the number of days increases. PROC REPORT will figure out how many columns are needed and give you the proper number of columns in the output. See Maxim 19.

Probably, you will want to do some summarization of the previous week before you get to PROC REPORT. This can be done via PROC SUMMARY, or otherwise. But let's assume you have done this.

Brief example:

data have;
    input product description $ total last_week day :date9. amount_received;
    cards;
1235    Item1    3173   1070    07FEB2021    73
1235    Item1    3173   1070    08FEB2021    34
5432    Item4    340     96     07FEB2021    19
5432    Item4    340     96     08FEB2021    2
6574    Item5     5       2     08FEB2021    3
;

proc report data=have;
    columns product description total last_week day,amount_received;
    define product/group;
    define description/group;
    define total/mean;
    define last_week/mean;
    define day/across format=downame. order=internal;
    define amount_received/mean;
run;

Please note that if you increase the data set, by adding more ROWS with other dates, the exact same PROC REPORT table will get wider, without you having to modify PROC REPORT.

Your task is to feed the proper data set into PROC REPORT, computing last_week via PROC SUMMARY or other method

Re: How to add dynamically weekday column counts in the report as the week progress?

Stalk — Wed, 10 Feb 2021 20:51:10 GMT

Works like a charm.