calculate field row by row.

[Shantaram]

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?

[ballardw]

What does your current SAS dataset look like?

[Kurt_Bremser]

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

[Shantaram]

Hi,

PFA for sample and requirement data.

[Reeza]

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

[Shantaram]

/*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

[Shantaram]

/*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

/*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. 

[Kurt_Bremser]

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-dat...

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% 

[Shantaram]

Hi KurtBremser,

THANKS...

Its working properly.

[Reeza]

Please mark Kurts response as thw correct
answer.