Calculations from a dataset 1

[SAS18]

Hi.This is the first time iam entering this forum. Please help me with this.

data results;
input category $ year gwp;
cards;
Reinstatement 2003 -104
Reinstatement 2004 -148
Reinstatement 2005 -14.25
Normal_Renewals 2003 125.25
Normal_Renewals 2004 145.36
Normal_Renewals 2005 -178.29
Canc_Agncy_Tfd 2003 -89.75
Canc_Agncy_Tfd 2004 450.00
Canc_Agncy_Tfd 2005 478.00
Canc_Clnt_Req 2003 -58.36
Canc_Clnt_Req 2004 -87.69
Canc_Clnt_Req 2005 -145.69
Canc_Lost_To_Unknown_Comp 2003 -159
Canc_Lost_To_Unknown_Comp 2004 -58.69
Canc_Lost_To_Unknown_Comp 2003 -78.69
Canc_With_Migration 2003 -48.69
Canc_With_Migration 2004 -58.78
Canc_With_Migration 2005 -45.69
;
run;

The dataset at work has about 136 observations and all the cancellation categories start with the
word "canc". Since they are about 80 observations that start with word "canc" it is difficult
to enter each and every cancelaltion category. In my calculation of overallresults below i would also want to exclude Canc_With_Migration which doesnt fall under the cancellation category. Please help me to produce
these results.

Calculation
overallresults=sum(Reinstatement_2003,Reinstatement_2004,Reinstatement_2005 )+
sum(Normal_Renewals_2003,Normal_Renewals_2004,Normal_Renewals_2005)+
sum(Canc_Agncy_Tfd_2003,Canc_Agncy_Tfd_2004,Canc_Agncy_Tfd_2005,
Canc_Clnt_Req_2003,Canc_Clnt_Req_2004,Canc_Clnt_Req_2005+
Canc_Lost_To_Unknown_Comp_2003,Canc_Lost_To_Unknown_Comp_2004,
Canc_Lost_To_Unknown_Comp_2005)

If you find any difficulties in understanding my query pls feel free to ask.

Regards,
SAS18

 

[klaz2002]

SAS18,
Perhaps you need to organize your data. First, try to build a dataset that has only the information that you would like to sum. You can build your logic using IF statements. When you have the data that you are looking for you could use the proc SQL with the sum option to add all the values up.

Write yourself some logic using pseudo code. For example, you could write your rules that will make up the types of cancelations you want to retain.
Ex.
rules
1) keep all records that have canc in the first 4 spaces.
2) Except if the rest of the string has _with_Migration.

You could also use PROC FREQ to give you a nice set of results.

Hope that this has helped you,
Klaz

 

[ChrisW75]

If all you are wanting is the total sum of the GWP column, then a proc summary will do that...

proc summary data=results(where=(category ne 'Canc_With_Migration'));
  var gwp;
  output out=results2 sum=;
run;

You should also put a length statement in your input step to define the length of the CATEGORY column, otherwise it'll default to a value that is too short and truncate the values.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.