topic Re: proc report - not totaling and not sorting correctly in SAS Programming

proc report - not totaling and not sorting correctly

Q1983 — Sun, 22 Dec 2019 23:03:28 GMT

data have;

infile datalines;

input setup_date date9. Type $ cnt;

return;

datalines;

1jun2019 Central 1

1jun2019 West 1

12jul2019 East 1

14aug2019 West 1

15aug2019 West 1

20aug2019 East 1

;run;

data have2;

set have;

monthsum = month(setup_date);/*returns numeric*/

Month1 = put(setup_date,monyy5.);/*change to character*/

format setup_date date9.;

run;

proc report data=have2 wrap style(column)={just=center};

column Type monthsum MONTH1 cnt ;

define Type / group style (column)=[cellwidth=100pt] "EXC TYPE";

define monthsum /group noprint order = internal;

define MONTH1 /across style(column)=[cellwidth=100pt] "MONTH";

define cnt / sum "Row Total" style(column)=[cellwidth=90pt];

rbreak after / summarize style (summary)= Header;

compute after;

Type = 'Grand Total';

endcomp;

run;

Issue#1 - The proc report appears not to roll up totals correctly. I get extra rows in the output

Issue#2 - The Month1 (across) is not sorting by month despite using the monthsum.

Re: proc report - not totaling and not sorting correctly

SASKiwi — Sun, 22 Dec 2019 23:21:40 GMT

Try replacing both monthsum and month1 with setup_date defined as an across column with order=internal and format monyy5:

define setup_date / across order = internal format = monyy5.;

Re: proc report - not totaling and not sorting correctly

Tom — Sun, 22 Dec 2019 23:33:06 GMT

What do you want out. Might be easiest to just make a date value that is the first of the month using the INTNX() function to use for your ACROSS variable.

data have;
  input setup_date :date9. Type :$11. cnt;
  format setup_date date9.;
datalines;
1jun2019 Central 1
1jun2019 West 1
12jul2019 East 1
14aug2019 West 1
15aug2019 West 1
20aug2019 East 1
;

proc print data=have; title 'HAVE'; run;

data have2;
  set have;
  Month2 = intnx('month',setup_date,0,'b');
  format month2 monyy7. ;
run;
proc print data=have2; title 'HAVE2'; run;

proc report data=have2 wrap style(column)={just=center};
title 'PROC REPORT';
  column Type cnt,month2 ;
  define Type / group style (column)=[cellwidth=100pt] "EXC TYPE";
  define MONTH2 /across order=internal style(column)=[cellwidth=100pt] "MONTH";
  define cnt / sum ' ' style(column)=[cellwidth=90pt];
  rbreak after / summarize style (summary)= Header;
  compute after;
    Type = 'Grand Total';
  endcomp;
run;
title;