BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
asdf12_12
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

5 REPLIES 5
BrunoMueller
SAS Super FREQ

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
Fluorite | Level 6

Thank you very much!

 

Regards,

Aksel

asdf12_12
Fluorite | Level 6

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

BrunoMueller
SAS Super FREQ

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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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