topic aggregation variable between two dates in SAS Programming

aggregation variable between two dates

Gieorgie — Thu, 04 Nov 2021 10:08:41 GMT

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;

Re: aggregation variable between two dates

PaigeMiller — Thu, 04 Nov 2021 11:20:00 GMT

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.

Re: aggregation variable between two dates

Gieorgie — Thu, 04 Nov 2021 12:03:30 GMT

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

Re: aggregation variable between two dates

PaigeMiller — Thu, 04 Nov 2021 11:48:56 GMT

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.

Re: aggregation variable between two dates

Gieorgie — Thu, 04 Nov 2021 12:04:34 GMT

i edited my above answear .with corrected output

Re: aggregation variable between two dates

PaigeMiller — Thu, 04 Nov 2021 12:28:10 GMT

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.

Re: aggregation variable between two dates

Gieorgie — Thu, 04 Nov 2021 12:33:31 GMT

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

Re: aggregation variable between two dates

PaigeMiller — Thu, 04 Nov 2021 12:36:04 GMT

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?

Re: aggregation variable between two dates

Gieorgie — Thu, 04 Nov 2021 12:49:37 GMT

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