SAS sum rows on YTD

[sameer112217]

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.

[ballardw · Accepted solution]

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.

[novinosrin]

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

[ballardw]

---

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

[sameer112217]

thanks for your reply