The SAS Output Delivery System and reporting techniques

PROC REPORT

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

PROC REPORT

Hi....I am having trouble getting the output in the proper form. The output results in that data forming a "Step" appearance for each "FiscalYear" as the output for the first FiscalYear in cells a3:d12 and the output for the second FiscalYear is in cells e13: h22 and so on. I want the all of the output along rows 3 - 12 across the columns. Thanks.

 

 

PROC REPORT DATA=Summary NOWD SPANROWS;

COLUMN ("Top 10 Sales Products" (COUNT FiscalYear,(ID PROD1 Number_Requests)));

DEFINE COUNT / DISPLAY 'Rank Number' STYLE(COLUMN)={TAGATTR='format:#,##'};

DEFINE FiscalYear / ACROSS 'Fiscal Year' STYLE(COLUMN)={borderwidth=1px FRAME=box rules=cols };

DEFINE ID / 'Product ID' ;

DEFINE PROD1 / 'Product Name';

DEFINE Number_Requests / 'Number Requests' STYLE(COLUMN)={TAGATTR='format:#,##'};

RUN;

QUIT;


Accepted Solutions
Solution
‎09-02-2016 08:07 PM
SAS Super FREQ
Posts: 8,820

Re: PROC REPORT

[ Edited ]

Hi:

  Your problem is not quite clear. You seem to be referring to Excel output, but do not explain how you are getting the report output into Excel. Can you show your ODS destination statements? How are you getting output into Excel: ODS HTML? ODS MSOFFICE2K? ODS CSV? ODS TAGSETS.EXCELXP or ODS EXCEL? Or ???

 

  Also, you did not post any data, so it is hard to figure out what is happening. But with COUNT being a DISPLAY variable and FiscalYear being an ACROSS variable, I can imagine that without a GROUP variable, you might get some odd results. The "step" appearance that you describe generally indicates the lack of a GROUPING variable to collapse the rows. See, for example, this paper http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf shows the "stair step" output by comparison between the outputs on page 6 and page 7.

 

  For a question like this, understanding the structure of your WORK.SUMMARY dataset is critical in order for anyone to make a suggestion. Can you post WORK.SUMMARY or make a "fake" dataset that mimics the structure?

 

  Here's another example of the "stairstep" vs using GROUP. I used HTML instead of any Excel-based destination because until you figure out the "stairstep" solution, there's no point in sending it to Excel:

step_with_report.png

 

cynthia

 

  Here's the code:

data work.snacks;
  length Product $25;
  infile datalines dlm=',' dsd;
  input Date : date9. Product $ tot_qty cnt_sales ;
datalines;
"01JAN2016, "Baked potato chips", 139, 30 
"01JAN2016, "Barbeque potato chips", 261, 37 
"01FEB2016, "Baked potato chips", 143, 25 
"01FEB2016, "Barbeque potato chips", 242, 29 
"01MAR2016, "Baked potato chips", 160, 33 
"01MAR2016, "Barbeque potato chips", 300, 37 
"01APR2016, "Baked potato chips", 125, 32 
"01APR2016, "Barbeque potato chips", 263, 36 
"01MAY2016, "Baked potato chips", 156, 34 
"01MAY2016, "Barbeque potato chips", 221, 38 
"01JUN2016, "Baked potato chips", 152, 32 
"01JUN2016, "Barbeque potato chips", 175, 36
;
run;
 
  proc report data=work.snacks;
    title '1) With Order usage for Product (results would be similar for DISPLAY usage)';
    column product date,(cnt_sales tot_qty);
    define product / order;
	define date / across f=monyy5. order=internal;
	define tot_qty / sum;
	define cnt_sales/ sum;
  run;

  proc report data=work.snacks;
    title '2) With Group usage for Product';
    column product date,(cnt_sales tot_qty);
    define product / group;
	define date / across f=monyy5. order=internal;
	define tot_qty / sum;
	define cnt_sales/ sum;
  run;

View solution in original post


All Replies
Solution
‎09-02-2016 08:07 PM
SAS Super FREQ
Posts: 8,820

Re: PROC REPORT

[ Edited ]

Hi:

  Your problem is not quite clear. You seem to be referring to Excel output, but do not explain how you are getting the report output into Excel. Can you show your ODS destination statements? How are you getting output into Excel: ODS HTML? ODS MSOFFICE2K? ODS CSV? ODS TAGSETS.EXCELXP or ODS EXCEL? Or ???

 

  Also, you did not post any data, so it is hard to figure out what is happening. But with COUNT being a DISPLAY variable and FiscalYear being an ACROSS variable, I can imagine that without a GROUP variable, you might get some odd results. The "step" appearance that you describe generally indicates the lack of a GROUPING variable to collapse the rows. See, for example, this paper http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf shows the "stair step" output by comparison between the outputs on page 6 and page 7.

 

  For a question like this, understanding the structure of your WORK.SUMMARY dataset is critical in order for anyone to make a suggestion. Can you post WORK.SUMMARY or make a "fake" dataset that mimics the structure?

 

  Here's another example of the "stairstep" vs using GROUP. I used HTML instead of any Excel-based destination because until you figure out the "stairstep" solution, there's no point in sending it to Excel:

step_with_report.png

 

cynthia

 

  Here's the code:

data work.snacks;
  length Product $25;
  infile datalines dlm=',' dsd;
  input Date : date9. Product $ tot_qty cnt_sales ;
datalines;
"01JAN2016, "Baked potato chips", 139, 30 
"01JAN2016, "Barbeque potato chips", 261, 37 
"01FEB2016, "Baked potato chips", 143, 25 
"01FEB2016, "Barbeque potato chips", 242, 29 
"01MAR2016, "Baked potato chips", 160, 33 
"01MAR2016, "Barbeque potato chips", 300, 37 
"01APR2016, "Baked potato chips", 125, 32 
"01APR2016, "Barbeque potato chips", 263, 36 
"01MAY2016, "Baked potato chips", 156, 34 
"01MAY2016, "Barbeque potato chips", 221, 38 
"01JUN2016, "Baked potato chips", 152, 32 
"01JUN2016, "Barbeque potato chips", 175, 36
;
run;
 
  proc report data=work.snacks;
    title '1) With Order usage for Product (results would be similar for DISPLAY usage)';
    column product date,(cnt_sales tot_qty);
    define product / order;
	define date / across f=monyy5. order=internal;
	define tot_qty / sum;
	define cnt_sales/ sum;
  run;

  proc report data=work.snacks;
    title '2) With Group usage for Product';
    column product date,(cnt_sales tot_qty);
    define product / group;
	define date / across f=monyy5. order=internal;
	define tot_qty / sum;
	define cnt_sales/ sum;
  run;
Regular Contributor
Posts: 228

Re: PROC REPORT

[ Edited ]

Hi Cynthia.....Thanks for your help. From your example, I noticed you had included order=internal on the define statement for the variable that with "Across" . I added that order=internal to the define statement for the variable FiscalYear as well as changed "Display" to "Group" on the define statememt for the variable Count and it worked but when it transposed the FiscalYears as headers for each column, only partial FiscalYears as names as displayed such as 2012 wheras the actual FiscalYear should have been 2012/13. Is there a  way to force it to display the complete name for the Fiscal Year....Thanks.

SAS Super FREQ
Posts: 8,820

Re: PROC REPORT

Hi:

  I used ORDER=INTERNAL so that the date columns would be ordered with January first, instead of in formatted order, which would have put April and August first. I generally use ORDER=INTERNAL with SAS date variables for that reason.
 
  I wonder whether your Fiscal Year variable is a character variable and not a SAS date variable. It is hard to speculate since you did not provide data. Generally, column headers are truncated because you use a format that is too small or because the dataset has a format assigned and that is too small.

  Or, to complicate matters, Excel does not always respect the SAS column widths and instead uses a general default width. So there are a number of things that could be causing your issues.

But, as I said, since you did not provide data or your complete code, so no one can run your code to see the same thing that you are observing.

  Sorry I can't be of more help.

cynthia

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 361 views
  • 0 likes
  • 2 in conversation