aggregation variable between two dates

[Gieorgie]

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;

[PaigeMiller]

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.

[Gieorgie]

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

[PaigeMiller]

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.

[Gieorgie]

i edited my above answear .with corrected output

[PaigeMiller · Accepted solution]

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.

[Gieorgie]

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

[PaigeMiller]

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?

[Gieorgie]

Now it works, I'm sorry, I'm new and the number of tasks and their scope are too big, thank you very much for your help