PROC REPORT

[twildone]

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;

[Cynthia_sas · Accepted solution]

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:

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;

[twildone]

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.

[Cynthia_sas]

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