topic Re: SAS sum rows on YTD in New SAS User

SAS sum rows on YTD

sameer112217 — Thu, 29 Nov 2018 16:43:38 GMT

I need some help. I have table like this

Policyno Premimumdate Type Premiumamount

11111 01Jan2018 Retail 100

2222 01FEB2018 Retail 200

3333 01MAR018 Corporate 300

4444 01APR2018 Corporate 100

5555 01JUN2018 Retail 100

6666 01JUL018 Retail 200

7777 01AUG2018 Corporate 300

8888 01SEP2018 Corporate 100

I need an output like this in the sum column. I need YTD (year till date) by type. YTD for last year starts from Apr 2017 to Mar 2018. Current YTD starts from Apr 2018 till date or latest end date. So in second row (policy 2222) for retail type it will be 100+200=300

Policyno Premimumdate Type Premiumamount Sum

11111 01Jan2018 Retail 100 100

2222 01FEB2018 Retail 200 300

3333 03MAR018 Corporate 300 300

4444 07APR2018 Corporate 100 100

5555 01JUN2018 Retail 100 100

6666 25JUL018 Retail 200 300

7777 01AUG2018 Corporate 300 400

8888 09SEP2018 Corporate 100 500

Can I sum based on conditions? I tried taking MONYY7. taking month and year and defining the criteria for fiscal year but how can i add the sum of all retail or corporate starting from Apr 2018 premium amount till premiumamount date values.

Re: SAS sum rows on YTD

ballardw — Thu, 29 Nov 2018 17:08:02 GMT

From you example desired output it appears that perhaps you have forgotten a rule involving the TYPE variable?

So you likely need some sorting. My approach would be to add a fiscal year variable to have a sort by that value, and the type.

I suspect that also misspelled Premiumdate but using your names:

data have;
   input Policyno $ Premimumdate :date9.  Type  :$15.       Premiumamount ;
   format Premimumdate date9.; 
   FiscalYr = year(Premimumdate)+ (month(Premimumdate)>3);
datalines;
11111        01Jan2018      Retail             100
2222         01FEB2018      Retail             200
3333         01MAR018       Corporate          300
4444         01APR2018      Corporate          100
5555         01JUN2018      Retail             100
6666         01JUL018       Retail             200
7777         01AUG2018      Corporate          300
8888         01SEP2018      Corporate          100
;
run;

proc sort data=have;
   by fiscalyr type Premimumdate;
run;

data want;
   set have;
   by fiscalyr type ;
   retain cum;
   if first.type then cum=0;
   cum+Premiumamount;
run;

Note the data step to create data. It is best to provide that for example data as otherwise we make all kinds of guesses about variable types.

Also provide such in a code box opened using the forum's {I} icon to preserve formatting.

Re: SAS sum rows on YTD

novinosrin — Thu, 29 Nov 2018 17:30:59 GMT

@ballardw Sir, Touch class and neat!!!!!!!!!! My salutations to the genie!

 FiscalYr = year(Premimumdate)+ (month(Premimumdate)>3);

Also, I vaguely remember @data_null__ showed something similar achieved using intervalds system optiona while ago. And I wish I saved that too.

Re: SAS sum rows on YTD

ballardw — Thu, 29 Nov 2018 20:53:02 GMT

@novinosrin wrote:

@ballardw Sir, Touch class and neat!!!!!!!!!! My salutations to the genie!
 FiscalYr = year(Premimumdate)+ (month(Premimumdate)>3);
Also, I vaguely remember @data_null__ showed something similar achieved using intervalds system optiona while ago. And I wish I saved that too.

This approach for a different type of year is flexible enough that if you have companies with different fiscal year/start end that with a variable to have the start or end month you can make this per company specific by adding the boundary month in.

Having dealt with state fiscal year, federal fiscal year, water year, school year and probably a few other "years" I don't recall at the moment this is a useful approach.

Re: SAS sum rows on YTD

sameer112217 — Fri, 30 Nov 2018 17:20:51 GMT

thanks for your reply