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

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

across_diff_structure.png

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

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ

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:

across_diff_structure.png

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
Diamond | Level 26

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.

 

 

--
Paige Miller
Cynthia_sas
SAS Super FREQ
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
Fluorite | Level 6

Thank you very much for the solution @Cynthia_sas

 

I also thank @PaigeMiller  for valuable inputs.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1228 views
  • 0 likes
  • 3 in conversation