BookmarkSubscribeRSS Feed
Shantaram
Calcite | Level 5

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?

 


sample.jpg
9 REPLIES 9
ballardw
Super User

What does your current SAS dataset look like?

Shantaram
Calcite | Level 5

Hi,

 

PFA for sample and requirement data.

Reeza
Super User

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
Calcite | Level 5

/*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
Calcite | Level 5

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

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
Calcite | Level 5

Hi KurtBremser,

 

THANKS...

Its working properly.

Reeza
Super User
Please mark Kurts response as thw correct
answer.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1831 views
  • 0 likes
  • 4 in conversation