Need help with across option in proc report

[jb9977]

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.

[PaigeMiller]

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.

[Cynthia_sas · Accepted solution]

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

[PaigeMiller]

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.

[Cynthia_sas]

Hi:
I understand completely. But even if the OP does a transpose, he still needs to calculate the Variance and the Total, which will require another DATA step program. Either way, whether Transpose is the solution with PROC PRINT or REPORT is the solution -- the data needs to be restructured -- that is the key to making this work in either case.

Cynthia

[jb9977]

Thank you very much for the solution @Cynthia_sas. 

I also thank @PaigeMiller  for valuable inputs.