01-04-2018
asdf12_12
Fluorite | Level 6
Member since
09-09-2016
- 14 Posts
- 3 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by asdf12_12
Subject Views Posted 3001 12-26-2017 06:17 AM 3085 12-22-2017 04:16 AM 3174 12-21-2017 10:30 AM 1982 12-21-2017 10:22 AM 1994 12-21-2017 10:10 AM 1995 12-21-2017 09:57 AM 2055 12-21-2017 04:36 AM 4588 10-13-2017 11:32 AM 4640 10-13-2017 05:53 AM 1910 10-03-2017 05:04 AM -
Activity Feed for asdf12_12
- Liked Re: PROC REPORT - Calculation of Mean and SUM based on row for Cynthia_sas. 01-04-2018 04:21 AM
- Liked Re: PROC REPORT - Calculation of Mean and SUM based on row for PaigeMiller. 12-28-2017 07:01 AM
- Posted Re: PROC REPORT - Calculation of Mean and SUM based on row on SAS Procedures. 12-26-2017 06:17 AM
- Posted Re: PROC REPORT - Calculation of Mean and SUM based on row on SAS Procedures. 12-22-2017 04:16 AM
- Posted PROC REPORT - Calculation of Mean and SUM based on row on SAS Procedures. 12-21-2017 10:30 AM
- Posted Re: PROC Report Grouping Dates (Across) by Year and Half. on SAS Procedures. 12-21-2017 10:22 AM
- Posted Re: PROC Report Grouping Dates (Across) by Year and Half. on SAS Procedures. 12-21-2017 10:10 AM
- Liked Re: PROC Report Grouping Dates (Across) by Year and Half. for BrunoMueller. 12-21-2017 09:58 AM
- Posted Re: PROC Report Grouping Dates (Across) by Year and Half. on SAS Procedures. 12-21-2017 09:57 AM
- Posted PROC Report Grouping Dates (Across) by Year and Half. on SAS Procedures. 12-21-2017 04:36 AM
- Posted Re: PROC Report Summary Column for Rows with different metrics on ODS and Base Reporting. 10-13-2017 11:32 AM
- Posted PROC Report Summary Column for Rows with different metrics on ODS and Base Reporting. 10-13-2017 05:53 AM
- Posted Re: How do I sum select groups in PROC REPORT on ODS and Base Reporting. 10-03-2017 05:04 AM
- Posted Re: How do I sum select groups in PROC REPORT on ODS and Base Reporting. 10-02-2017 05:45 AM
- Posted Re: How do I sum select groups in PROC REPORT on ODS and Base Reporting. 09-25-2017 03:58 AM
- Posted Re: How do I sum select groups in PROC REPORT on ODS and Base Reporting. 09-22-2017 10:59 AM
- Posted How do I sum select groups in PROC REPORT on ODS and Base Reporting. 09-22-2017 09:25 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 1 1
12-26-2017
06:17 AM
Thank you very much as always.
... View more
12-22-2017
04:16 AM
Thank you very much to both, I will try both approaches. I am a bit hesitant to use PROC Tabulate as my real code groups the rows and adds aggregates (totals) after certain groups. I would like to keep this. Can Proc Tabulate replicate this as well. Not very versed in this Procedure. 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; Which gives me this output: As you can see I am adding totals to certain groups and not others. I will have a look at PROC Tabulate and see if I can create this output. Perhaps I should just work on my source data and get that aligned first to make it easier. My goal was to group the dates as Years and Quarters which I have now achieved thanks to a community member. The only thing left is to sum or mean based on the grouping. I will give PROC Tabulate a try. Thanks all again, Aksel
... View more
12-21-2017
10:30 AM
Dear all, I have this sample data giving me this sample report. I have row A and row B. I would like the calculations to be either Mean or Sum based on the Row values. ie if in the first column row is A then Sum if it is B then mean. 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; Which gives me: I would like the values in row A to be means and have the row B as sums (as it already is). Your help is much appreciated. Regards,
... View more
12-21-2017
10:10 AM
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
... View more
12-21-2017
09:57 AM
Thank you very much! Regards, Aksel
... View more
12-21-2017
04:36 AM
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
... View more
10-13-2017
11:32 AM
Thank you very much! I was worried that the first part of my ask was a bit confusing, but this will work great. Regards. Aksel
... View more
10-13-2017
05:53 AM
Dear all, I have managed to create a report (thanks to all the help from Mr.Bruno_SAS) exactly the way I wanted by using: PROC REPORT DATA=WORK.RANKED_New
STYLE(HEADER) = {font_size=6pt}
STYLE(COLUMN) = {font_size=6pt}
STYLE(REPORT) = {font_size=6pt cellpadding=2px}
;
COLUMNS Ranking Grouping Ranking_sub METRIC_LEVEL_2 Amounts ,SUMMARY_DATE _dummy;
WHERE YEAR ne 2016;
BY BI_Marker;
DEFINE Ranking / GROUP '' Left noprint;
DEFINE Grouping / Group order =internal '' Left;
DEFINE Ranking_sub / GROUP '' Left noprint;
DEFINE METRIC_LEVEL_2 / GROUP '';
DEFINE SUMMARY_DATE / across order=internal 'FY 2017' ; /*ddmmyyd10.*/
DEFINE AMOUNTS / ANALYSIS SUM FORMAT=COMMA20.2 '';
DEFINE _dummy / computed noprint;
break after ranking / summarize;
COMPUTE _dummy / CHAR LENGTH = 21;
_dummy = catx(":",ranking, _break_);
IF lowcase(_break_) = "ranking" AND ranking not in ('a','b','e','f','i') THEN DO;
METRIC_LEVEL_2 = "Total";
call missing(ranking);
END;
IF lowcase(_break_) = "ranking" AND ranking in ('a','b','e','f','i') THEN DO;
call missing(ranking, METRIC_LEVEL_2);
call missing(_c3_, _c4_,_c5_, _c6_,_c7_, _c8_,_c9_, _c10_,_c11_, _c12_,_c13_, _c14_,_c15_,_c16_);
END;
ENDCOMP;
COMPUTE AFTER Ranking;
LINE '';
ENDCOMP;
RUN; This creates this output: (not the best image, had to be redacted) I would like to create a column at the end of 31DEC2016 called FY2016 and for each row apply a different calculation. ie. for the Avg Asset Balances row, it should be straight average for the corresponding row under FY2016 for Comission/Fess, Income, Cost metrics should be aggregations (sum of the whole year) I was thinking perhaps a dummy column with an if statement? something like, compute dummy2; if Metric_Level_2 = 'Avg Asset Balances' then average(_c3_,_c4_,_c5_ etc) end; if METRIC_LEVEL_2 = 'Commission/Fees' then sum(_c3_,_c4_,_c5_ etc) end; endcomp; I know that I can refer to the columns in calculations, but I was wondering can we refer to rows as well in SAS? _r1_, _r2_ etc. as I would also like to calculate differences within the newly created FY2016 column. I know a lot to ask, but any direction, sample, report, paper would be more than appreciated. Thanks, Aksel
... View more
10-03-2017
05:04 AM
Oh wow! Thank you! You have gone out of your way to sort my troubles. Thank you very much for going the extra distance..
... View more
10-02-2017
05:45 AM
Thank you very much for your reply and apologies for my delayed reply. I am trying to get the business to give me access to the folder. Mean time, I will search and see if I can download it from another place. Thanks again for your help. Aksel
... View more
09-25-2017
03:58 AM
Just a quick follow up question: Is it possible to do calculations based on rows/columns? ie. Can I subtract the total from one specific line from another line? Now that I know how to assign dummy values to identify the rows to display data, I was wondering if it was possible to do calculations. I have been trying to find some articles regarding this, but it seems like it would be easier to do it at the data gathering stage rather than reporting. Regards, Aksel
... View more
09-22-2017
10:59 AM
Thank you very much!!! Worked like a charm.
... View more
09-22-2017
09:25 AM
Dear all, I have this code: PROC REPORT DATA=WORK.REPORT_DATA ; COLUMNS Metricrank METRIC_LEVEL_2 Amounts ,SUMMARY_DATE ; WHERE YEAR ne 2017; DEFINE METRICRANK / GROUP FORMAT=Metricreorder. order=internal ''; DEFINE METRIC_LEVEL_2 / GROUP ''; DEFINE SUMMARY_DATE / across order=internal 'FY 2016' FORMAT=YYMMD7.; DEFINE AMOUNTS / ANALYSIS SUM FORMAT=COMMA20.2 ''; Compute COMPUTE AFTER METRICRANK; LINE ''; ENDCOMP; RUN; which generates a report as such (without the Totals) Metricrank METRIC_LEVEL_2 31/01/2016 29/02/2016 31/03/2016 30/04/2016 31/05/2016 30/06/2016 31/07/2016 31/08/2016 30/09/2016 31/10/2016 30/11/2016 31/12/2016 31/01/2017 28/02/2017 31/03/2017 30/04/2017 31/05/2017 30/06/2017 31/07/2017 31/08/2017 1 Avg Debit Balances 1,933.19 1,928.39 1,909.33 1,980.59 1,977.09 1,992.60 1,986.33 1,977.66 1,956.16 2,016.96 2,024.24 2,041.70 2,019.76 1,929.44 1,934.39 1,933.58 1,914.24 1,898.88 1,887.41 1,862.88 RWA 1,861.07 1,868.95 1,851.91 1,897.52 1,891.22 1,935.29 1,925.81 1,925.42 1,914.89 1,989.04 1,716.48 1,716.35 1,681.40 1,606.18 1,599.92 1,614.26 1,565.14 1,532.58 1,503.88 2 Commission / Fees 0.05 0.04 0.08 0.14 0.06 0.09 0.06 0.07 0.10 0.14 0.06 0.06 0.07 0.10 0.12 0.09 0.11 0.07 0.09 0.09 Fund Transfer Pricing -1.10 -1.03 -1.07 -1.06 -1.09 -1.06 -1.09 -1.09 -1.04 -1.11 -1.08 -1.13 -1.12 -1.10 -1.09 -1.05 -1.09 -1.05 -1.08 -1.08 Income 3.37 3.35 2.98 3.95 3.62 3.37 3.80 3.67 3.16 4.64 3.61 3.46 3.40 3.88 3.22 3.32 3.29 -0.04 0.05 -0.88 Net Interest Income 0.16 0.15 0.18 0.22 0.24 0.22 0.22 0.21 0.19 0.22 0.21 0.22 0.22 0.19 0.23 0.24 0.24 3.89 4.42 4.72 Total: 2.49 2.52 2.16 3.25 2.83 2.61 2.99 2.86 2.40 3.90 2.81 2.61 2.57 3.07 2.47 2.61 2.55 2.87 3.48 2.86 3 Allocated Cost -1.05 -1.14 -0.95 -1.22 -1.23 -1.31 -1.22 -0.77 -1.54 -0.76 -0.75 -1.57 -1.19 -0.87 -0.99 -1.36 -1.31 -1.09 -0.60 Client Variable Cost -0.48 -1.01 -0.73 -0.55 -0.63 -0.52 -0.61 -0.53 -0.54 -0.70 -0.58 -0.61 -0.77 -0.83 -0.70 -0.67 -0.65 -0.75 -0.71 -0.58 Portfolio Variable Cost -0.07 -1.27 -0.69 -0.64 -0.69 -0.77 -0.42 -0.79 -0.25 -1.28 -1.10 -0.99 -0.63 -0.57 -0.88 -0.64 -0.26 -0.36 -1.11 -0.13 Total: -1.60 -3.41 -2.36 -2.41 -2.55 -2.60 -2.25 -2.09 -2.33 -2.74 -2.43 -3.17 -2.59 -2.28 -2.57 -2.68 -2.23 -2.20 -2.42 -0.71 As you can see, I would like to summarize only Metricrank 2 and 3 to get a net figure. Is this possible? I was trying Compute with if statements but wasn't successful. Your help is much appreciated. Regards, Aksel
... View more