I have such a table and I would like to sum or group the table so as to reduce the number of date results and name this position prior (so that they are summed up to 2 months before today's date)
what i have :
would i expect
POLICY_VINTAGE | EXPIRED_TODAY |
prior | 23232 |
2021-09 | 3 |
2021-10 | 1 |
2021-11 | 1 |
My sample code:
proc sql;
create table diff_policy as
select today.policy_vintage
, today.EXPIREDas EXPIRED_TODAY
, prior.EXPIREDas EXPIRED_PRIOR
, today.ACTIVE as ACTIVE_TODAY
, prior.ACTIVE as ACTIVE_PRIOR
, today.EXPIRED- prior.EXPIRED as DIFFRENCE_E
, today.ACTIVE- prior.ACTIVE as DIFFRENCE_A
from tablea_new today
LEFT JOIN
(select *
from _work.policy_weekly
where run_date < today()
and today.policy_vintage between today.policy_vintage and &thismonth.-2
having run_date = max(run_date)
) prior
ON today.policy_vintage = prior.policy_vintage
;
quit;
So assuming you have numeric dates (do you?) then this will work:
/* UNTESTED CODE */
proc format;
value vintf low-'31JUL2021'd = 'prior' other=[yymmd7.];
run;
proc summary data=have nway;
class policy_vintage;
format policy_vintage vintf.;
var expired_today;
output out=want sum=;
run;
If your dates are not numeric, then they should be numeric and you should convert them to numeric.
The code is untested. If you want tested code, please provide the data in the requested format.
I can't see how you get the expected output with dates in 2021 from the original data, which has no dates in 2021. Please explain.
In the future, we request that you present data as SAS data step code (instructions) instead of as a screen capture or file attachment. We cannot work with screen captures or file attachments.
Thanks for the answer, I didn't show the result for 2021 because there are a lot of records between the 1 and the row where the date is. I would like to add up everything by 2021 to minimize the appearance of the table SAMPLE DATA
POLICY_VINTAGE | EXPIRED_TODAY |
2008-11 | 1 |
2010-01 | 3 |
2010-03 | 1 |
2010-05 | 1 |
2010-06 | 2 |
2010-07 | 1 |
2010-09 | 1 |
2021-08 | 365 |
2021-09 | 411 |
2021-10 | 874 |
2021-11 | 93 |
This is expect result :I would like to sum up all the results by 2021 and call them prior
POLICY_VINTAGE | EXPIRED_TODAY |
prior | 10 |
2021-08 | 365 |
2021-09 | 411 |
2021-10 | 874 |
2021-11 | 93 |
I'm still not sure I can connect the input to the desired output. If I can't do that, I can't write code. Your desired output shows 2021-09 with a value of 3.
Repeating:
In the future, we request that you present data as SAS data step code (instructions) instead of as a screen capture or file attachment. We cannot work with screen captures or file attachments.
So assuming you have numeric dates (do you?) then this will work:
/* UNTESTED CODE */
proc format;
value vintf low-'31JUL2021'd = 'prior' other=[yymmd7.];
run;
proc summary data=have nway;
class policy_vintage;
format policy_vintage vintf.;
var expired_today;
output out=want sum=;
run;
If your dates are not numeric, then they should be numeric and you should convert them to numeric.
The code is untested. If you want tested code, please provide the data in the requested format.
Thank you very much for your help, I can see that it is getting closer. Policy_Vinatge is a date, not numeric. And after the proc the format changed to numeric.
now I would like to sum up all the results that meet the proc format as prior.
proc sql; create table diff_policy as select today.policy_vintage format vintf. , today.EXPIRED as EXPIRED_TODAY , prior.EXPIRED as EXPIRED_PRIOR , today.ACTIVE as ACTIVE_TODAY , prior.ACTIVE as ACTIVE_PRIOR , today.EXPIRED- prior.EXPIRED as DIFFRENCE_E , today.ACTIVE- prior.ACTIVE as DIFFRENCE_A from tablea_new today LEFT JOIN (select * from _work.policy_weekly where run_date < today() having run_date = max(run_date) ) prior ON today.policy_vintage = prior.policy_vintage ; quit;
what i get
POLICY_VINTAGE | EXPIRED_TODAY |
prior | 2 |
prior | 1 |
prior | 1 |
2021-08 | 365 |
2021-10 | 874 |
2021-11 | 93 |
what i need
POLICY_VINTAGE | EXPIRED_TODAY |
prior | 4 |
2021-08 | 365 |
2021-10 | 874 |
2021-11 | 93 |
after proc contenst
# | Typ | Dł. | Format | |
---|---|---|---|---|
5 | ACTIVE_PRIOR | Num | 8 | |
4 | ACTIVE_TODAY | Num | 8 | |
7 | DIFFRENCE_A | Num | 8 | |
6 | DIFFRENCE_E | Num | 8 | |
3 | EXPIRED_PRIOR | Num | 8 | |
2 | EXPIRED_TODAY | Num | 8 | |
1 | POLICY_VINTAGE | Num | 8 | YYMMD7 |
I don't understand why you show PROC SQL code here, nothing I did requires SQL. What happens when you run the code I showed?
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!
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.