PROC Report Grouping Dates (Across) by Year and Half.

[asdf12_12]

Dear all,

I have successfully managed to get a report out using PROC Report the way I wanted by using this code:

PROC REPORT DATA=WORK.RANK_ALL;
COLUMNS Rank Grouping REGROUPING Amounts, Summary_date ('Overall' AMOUNTS = TotalAmount) _dummy ;
WHERE SEGMENT_Lvl_1 EQ 'Bespoke' AND YEAR(SUMMARY_DATE)ne 2017;
DEFINE Rank / Group '' NOPRINT;
DEFINE Grouping / GROUP ORDER=INTERNAL '' LEFT;
DEFINE REGROUPING / GROUP '' ;
DEFINE SUMMARY_DATE / ACROSS ORDER=INTERNAL '' FORMAT=DDMMYYd10.; /*mmyyd.*/
DEFINE AMOUNTS / ANALYSIS SUM '' FORMAT=COMMA20.2;
DEFINE _dummy / COMPUTED '' NOPRINT;
DEFINE TotalAmount / SUM 'Total' NOPRINT;
BREAK AFTER Rank / SUMMARIZE STYLE={foreground=black fontweight=bold fontstyle=italic};
COMPUTE _dummy / CHAR LENGTH = 21;
	_dummy = catx(":", Rank, _break_);
		IF lowcase(_break_) = "rank" AND Rank NOT IN ('a','b','e','f','i','j','k','l','m') THEN DO;
		REGROUPING = "Total";
		CALL MISSING(Rank);
		END;
		IF lowcase(_break_) = "rank" AND Rank IN ('a','b','e','f','i','j','k','l','m') THEN DO;
		CALL MISSING(Rank, REGROUPING);
		CALL MISSING(_c3_,_c4_,_c5_,_c6_,_c7_,_c8_,_c9_,_c10_,_c11_,_c12_,_c13_,_c14_,_c15_,_c16_,_c17_,_c18_,_c19_,_c20_,_c21_,_c22_,_c23_,_c24_,_c25_,_c26_);
		END;

ENDCOMP;
COMPUTE AFTER Rank;
	LINE '';
ENDCOMP;


RUN;

This data grabs a ranked table and displays something like this:

As you can see I have a dates from Jan 16 up until Nov 2017.  What I want to be able to do is to group the Dates so that 2016 is one group, and since 2017 is not complete, have it as 2017 H1 and 2017 H2.  So basically the idea is to reduce the columns down to 3.

Any help or guidance would be most welcomed.

Regards,

Aksel

[BrunoMueller · Accepted solution]

You can use nested formats to achieve this. Basically you specify a date range and the porper format to use.

Below is an example that shows this.

Currently the current year is hard coded, but this can be made dynamic as well

data have;
  do groupValue = "A", "B";
    do someDate = intnx("year", today(), -2, "B") to mdy(8,15, year(today()));
      value = ceil(ranuni(0) * 1000);
      output;
    end;
  end;
  format
    someDate date9.
    value commax14.
  ;
run;

proc format;
  value year_current
    low - "31dec2016"d = [year4.]
    "01jan2017"d - "31jul2017"d = "2017H1"
    "01aug2017"d - "31dec2017"d = "2017H2"
  ;
run;

proc report data=have;
  column groupValue someDate, value;
  define groupValue / group;
  define someDate / across format=year_current.;
run;

[asdf12_12]

Thank you very much!

Regards,

Aksel

[asdf12_12]

One follow up question if I may;

Can I do separate calculations on each row?  I want row A values to be AVERAGE (mean) and row B to be SUM.

Is this possible?

Regards,

Aksel

[BrunoMueller]

Please open a new discussion, with the appropriate title, this makes it easier for everyone.

For better understanding provide some test data or use data from SASHELP.

thanks for understanding.

[asdf12_12]

Of course.