topic Re: calculate field row by row. in ODS and Base Reporting

calculate field row by row.

Shantaram — Tue, 29 Mar 2016 14:02:02 GMT

Hi,

I want to calculate row lavel data first calculate sum then divide with sum.

EX.

PFA

Now i am doing same in excel but i want to automated in proc report.

Formulas -

YTD Feb15=105171/ 316270*100=33.3%

YTD Feb15=211099/ 316270*100=66.7%

but i dont have Inforce Total column, which is sum of columns.

How to do in sas proc report?

Re: calculate field row by row.

ballardw — Tue, 29 Mar 2016 14:21:37 GMT

What does your current SAS dataset look like?

Re: calculate field row by row.

Kurt_Bremser — Tue, 29 Mar 2016 14:24:04 GMT

If you provide test data (https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712), we can more easily help you.

Re: calculate field row by row.

Shantaram — Tue, 29 Mar 2016 14:49:37 GMT

Hi,

PFA for sample and requirement data.

Re: calculate field row by row.

Reeza — Tue, 29 Mar 2016 15:42:32 GMT

A lot of responders cannot download files, especially in corporate environments. Including data in your post increases the number of responses you'll get.

Re: calculate field row by row.

Shantaram — Wed, 30 Mar 2016 05:04:10 GMT

/*Input Data*/

Product_mix Total_data Appt
Inforce-Traditional 116937 27006
Inforce-ULIP 104128 16971
Lapse-Traditional 294386 22820
Lapse-ULIP 434419 24296
Surrender-Traditional 118209 6347
Surrender-ULIP 659858 27250

Re: calculate field row by row.

Shantaram — Wed, 30 Mar 2016 05:11:00 GMT

/*Input Data*/

/*Output Wants*/
Product Mix Data Appt %Total Data
YTD_Feb16 TD_Feb16 YTD Feb16
Inforce-Traditional 116937 27006 52.8
Inforce-ULIP 104128 16971 47.1
Inforce Total 221065 43977 100
Lapse-Traditional 294386 22820 40.3
Lapse-ULIP 434419 24296 59.6
Lapse Total 728805 47116 100
Surrender-Traditional 118209 6347 15.1
Surrender-ULIP 659858 27250 84.8
Surrender Total 778067 33597 100

Formula for-%Total Data=116937 / 221065*100

How to get inforce total row and how to divide with other rows.

Re: calculate field row by row.

Kurt_Bremser — Wed, 30 Mar 2016 06:04:27 GMT

For your convenience, I also added a data step to create the raw data, as described in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

data have;
length
  product_mix $25
  total_data 8
  appt 8
;
input
  product_mix
  total_data
  appt
;
cards;
Inforce-Traditional 116937 27006
Inforce-ULIP 104128 16971
Lapse-Traditional 294386 22820
Lapse-ULIP 434419 24296
Surrender-Traditional 118209 6347
Surrender-ULIP 659858 27250
;
run;

* create a group key;
data int;
set have;
length group $10;
group = scan(product_mix,1,'-');
run;

proc summary data=int;
by group;
var total_data;
output out=int2 (drop=_freq_ _type_ rename=(total_data=sum_total)) sum=;
run;

data want;
merge
  int
  int2
;
by group;
format total_percent percent8.1;
total_percent = total_data / sum_total;
run;

* create as report;
proc report data=want;
column group product_mix total_data appt total_percent;
define group / group;
define product_mix / display;
define total_data / analysis sum;
define appt / analysis sum;
define total_percent / analysis sum;
break after group / summarize;
run;

* create as data;
data want2;
set want;
by group;
if first.group
then do;
  sum_total = 0;
  sum_appt = 0;
end;
sum_total + total_data;
sum_appt + appt;
output;
if last.group then do;
  product_mix = 'Sum ' !! group;
  total_data = sum_total;
  appt = sum_appt;
  total_percent = 1;
  output;
end;
drop group sum_total sum_appt;
run;

The list output looks like this:

                                                                      total_dat             total_pe
                               group       product_mix                        a       appt     rcent
                               Inforce     Inforce-Traditional           116937      27006    52.9% 
                                           Inforce-ULIP                  104128      16971    47.1% 
                               Inforce                                   221065      43977   100.0% 
                               Lapse       Lapse-Traditional             294386      22820    40.4% 
                                           Lapse-ULIP                    434419      24296    59.6% 
                               Lapse                                     728805      47116   100.0% 
                               Surrender   Surrender-Traditional         118209       6347    15.2% 
                                           Surrender-ULIP                659858      27250    84.8% 
                               Surrender                                 778067      33597   100.0%

Re: calculate field row by row.

Shantaram — Wed, 30 Mar 2016 07:39:22 GMT

Hi KurtBremser,

THANKS...

Its working properly.

Re: calculate field row by row.

Reeza — Thu, 31 Mar 2016 01:26:23 GMT

Please mark Kurts response as thw correct
answer.