Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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:

Report.JPG

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


Accepted Solutions
Solution
‎12-21-2017 09:56 AM
SAS Super FREQ
Posts: 825

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

Posted in reply to asdf12_12

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;

View solution in original post


All Replies
Solution
‎12-21-2017 09:56 AM
SAS Super FREQ
Posts: 825

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

Posted in reply to asdf12_12

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;
Occasional Contributor
Posts: 14

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

Posted in reply to Bruno_SAS

Thank you very much!

 

Regards,

Aksel

Occasional Contributor
Posts: 14

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

Posted in reply to Bruno_SAS

One follow up question if I may;

 

Capture.JPG

 

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

SAS Super FREQ
Posts: 825

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

Posted in reply to asdf12_12

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.

Occasional Contributor
Posts: 14

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

Posted in reply to Bruno_SAS
Of course.
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 251 views
  • 1 like
  • 2 in conversation