Hi,
I have the following data
Date Day Planned Actual
20Jan2019 Sunday 500 440
21Jan2019 Monday 550 569
22Jan2019 Tuesday 450 746
23Jan2019 Wednesday 600 641
24Jan2019 Thursday
25Jan2019 Friday
26Jan2019 Saturday
And i am trying to get the output as follows using proc report
Date 20Jan2019 21Jan2019 22Jan2019 23Jan2019 24Jan2019 25Jan2019 26Jan2019 Week
DayName Sunday Monday Tuesday Wednesday Thursday Friday Saturday till date
Planned 500 550 450 600 . . . 2100
Actual 440 569 746 641 . . . 2396
Variance -12.00% 3.45% 65.78% 6.83% . . . 14.10%
The data will be updated for each day and if there is no data it should be blank.
Thanks for the help in advance.
Hi:
PROC REPORT will produce this kind of report, as shown below. However, the data that you have will need to be restructured as described:
Report #1 shows the structure that you need for PROC REPORT. You'll also have to calculate the Variance number for every date because it is a percent, not a sum or an average. You need a calculation for this number. PROC REPORT can make the percent formatted as you want using a CALL DEFINE.
Here's how the data was created for the above report:
data fakedata(keep=catord date dayname cat num);
length dayname $10 cat $12;
infile datalines;
input date : date. DayName $ Planned Actual;
catord = 1;
cat='Planned';
num = Planned;
output;
catord = 2;
cat='Actual';
num = Actual;
output;
catord=3;
cat='Variance';
num = (Actual - Planned) / Planned;
output;
return;
datalines;
20Jan2019 Sunday 500 440
21Jan2019 Monday 550 569
22Jan2019 Tuesday 450 746
23Jan2019 Wednesday 600 641
24Jan2019 Thursday . .
25Jan2019 Friday . .
26Jan2019 Saturday . .
;
run;
proc print data=fakedata (obs=6);
title '1) What does data look like';
var catord cat date dayname num;
format date date9.;
run;
And here's how the report #2 was created:
proc report data=fakedata nocompletecols;
title '2) Using PROC REPORT and Across';
column catord cat date,dayname,num ('Total' num=tot);
define catord / group noprint;
define cat / group ' ';
define date / across f=date9. order=internal;
define dayname / across ' ';
define num / sum ' ';
define tot / sum ' ';
compute num;
if cat = 'Variance' then
call define(_col_,'format','percentn9.2');
endcomp;
compute tot;
if cat = 'Planned' then ptot = tot;
else if cat = 'Actual' then atot = tot;
if cat = 'Variance' then do;
tot = (atot - ptot) / ptot;
call define(_col_,'format','percentn9.2');
end;
endcomp;
run;
Hope this helps,
Cynthia
PROC REPORT seems to me to be a poor choice for this type of output. PROC REPORT usually wants categories in the leftmost column to identify the rows, and you don't have that here. Although you can trick PROC REPORT into thinking that Planned and Actual and Variance are categories, they are not, and so you'd have to write the code to perform some trick to make that so, and then this could get tricky (see what I did there?) to maintain.
It almost seems as if you could transpose your original data and then PROC PRINT ought to get you the desired results.
OR
Print/Report the data in the original layout, rather than the transposed layout with days going left to right.
Hi:
PROC REPORT will produce this kind of report, as shown below. However, the data that you have will need to be restructured as described:
Report #1 shows the structure that you need for PROC REPORT. You'll also have to calculate the Variance number for every date because it is a percent, not a sum or an average. You need a calculation for this number. PROC REPORT can make the percent formatted as you want using a CALL DEFINE.
Here's how the data was created for the above report:
data fakedata(keep=catord date dayname cat num);
length dayname $10 cat $12;
infile datalines;
input date : date. DayName $ Planned Actual;
catord = 1;
cat='Planned';
num = Planned;
output;
catord = 2;
cat='Actual';
num = Actual;
output;
catord=3;
cat='Variance';
num = (Actual - Planned) / Planned;
output;
return;
datalines;
20Jan2019 Sunday 500 440
21Jan2019 Monday 550 569
22Jan2019 Tuesday 450 746
23Jan2019 Wednesday 600 641
24Jan2019 Thursday . .
25Jan2019 Friday . .
26Jan2019 Saturday . .
;
run;
proc print data=fakedata (obs=6);
title '1) What does data look like';
var catord cat date dayname num;
format date date9.;
run;
And here's how the report #2 was created:
proc report data=fakedata nocompletecols;
title '2) Using PROC REPORT and Across';
column catord cat date,dayname,num ('Total' num=tot);
define catord / group noprint;
define cat / group ' ';
define date / across f=date9. order=internal;
define dayname / across ' ';
define num / sum ' ';
define tot / sum ' ';
compute num;
if cat = 'Variance' then
call define(_col_,'format','percentn9.2');
endcomp;
compute tot;
if cat = 'Planned' then ptot = tot;
else if cat = 'Actual' then atot = tot;
if cat = 'Variance' then do;
tot = (atot - ptot) / ptot;
call define(_col_,'format','percentn9.2');
end;
endcomp;
run;
Hope this helps,
Cynthia
Yes, @Cynthia_sas , I have done this at one point in my life to force PROC REPORT to create such output. My conclusion was that it isn't worth the considerable amount of effort to make it work, and produces code that is difficult to read and understand. (By the way, as I recall, once I had accomplished something like this in PROC REPORT, the client then asked for more rows to be added to the report, requiring more complicated programming, and it was a very frustrating experience).
I much prefer the simple PROC PRINT of the data in the original format. And unless your job is to produce custom formatted reports (as opposed to reporting the data), I'd recommend not doing this via PROC REPORT.
Again, it appears that if you TRANSPOSE the original data set and then PROC PRINT, you can avoid all of this complication of getting PROC REPORT to work.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.