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.
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.
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.
@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.
@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.
thanks for your reply
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.