topic Re: How to determine the active record for the last month? in SAS Programming

How to determine the active record for the last month?

Babloo — Tue, 27 Nov 2018 13:05:54 GMT

I've a data as mentioned below in which I'm applying filter (current_ind=1) to determine the active record for the current month. If I run the DI job for this month, I will get only the last record from the below data in my output. However If I run the job for March, I should get the last before record (where POLICY_VERSION_ISSUE_DT=20/feb/18)in my output. That was the record which was active during March. I guess CURRENT_IND and POLICY_VERSION_ISSUE_DT is clue to determine the record for my output when I run the job for the past time periods. Could you please help me with the condition to determine the active policy for the past time periods? Please ignore the format issue in POLICY_VERSION_ISSUE_DT.

I run the job as follows in Putty. First script is for current time period and the other one is for March.

sh jobskeleton_std_01.sh uds_ex_details_tbfr10_13_50 D20181127:00:00:00*N*N uds_ex_details_tbfr10_13_50.log

sh jobskeleton_std_01.sh uds_ex_details_tbfr10_13_50 D20180327:00:00:00*N*N uds_ex_details_tbfr10_13_50.log

POLICY_ID	VALID_FROM_DTTM	VALID_TO_DTTM	PROCESSED_DTTM	CURRENT_IND	POLICY_VERSION_ISSUE_DT	POLICY_VERSION
2011077371	17MAY2018:00:00:03	17MAY2018:00:00:03	17MAY2018:13:00:00	0	05/aug/15	1
2011077371	17MAY2018:00:00:04	17MAY2018:00:00:04	17MAY2018:13:00:00	0	15/jun/17	3
2011077371	17MAY2018:00:00:05	17MAY2018:00:00:05	17MAY2018:13:00:00	0	24OCT2017	5
2011077371	17MAY2018:00:00:06	17MAY2018:00:00:06	17MAY2018:13:00:00	0	16/nov/17	6
2011077371	17MAY2018:00:00:07	17MAY2018:00:00:07	17MAY2018:13:00:00	0	02/dec/17	7
2011077371	17MAY2018:00:00:08	17MAY2018:00:00:08	17MAY2018:13:00:00	0	18/jan/18	8
2011077371	17MAY2018:00:00:09	26OCT2018:23:59:59	28OCT2018:19:10:00	0	20/feb/18	9
2011077371	27OCT2018:00:00:00	31DEC4747:23:59:59	28OCT2018:19:10:00	1	26OCT2018	12

Re: How to determine the active record for the last month?

DanielLangley — Tue, 27 Nov 2018 16:09:06 GMT

The below code is one possible way of solving your problem. You will have to adapt it for your specific use.

/*create some fake dates as examples*/
data have;
attrib randomdate format = ddmmyys10.;
do randomdate = "1JAN2018"d to "1JAN2019"d by 14;
	output;
end;
run;

/*I want the dates in the most recent order first
so that when i select the first observation it is the most recent*/
proc sort 	data = have
			out = havesorted;
by descending randomdate;
run;

/*Two different datasets both using the same style,
they will return everything from the table for the observation requested
If you want different date just change the date in the where clause.
*/
data wantToday;
set havesorted(obs = 1 where = ( randomdate lt today() ));
run;

data wantOld;
set havesorted(obs = 1 where = ( randomdate lt "1MAR2018"d ));
run;

Re: How to determine the active record for the last month?

Babloo — Wed, 28 Nov 2018 05:31:26 GMT

Any other alternative way to achieve this?

Re: How to determine the active record for the last month?

DanielLangley — Wed, 28 Nov 2018 10:06:31 GMT

There are probably 100 different ways to solve this problem. Why exactly does this solution not work? Do you need it to be faster? To not sort the data?

Re: How to determine the active record for the last month?

Babloo — Wed, 28 Nov 2018 12:20:49 GMT

yes, I no need to sort the data.

Re: How to determine the active record for the last month?

DanielLangley — Wed, 28 Nov 2018 13:32:25 GMT

If you cannot sort your data there is a deeper problem. I would also have a look into that if I were you.

How about this?

data have;
attrib randomdate format = ddmmyys10.;
retain PolicyNumber 0;
do randomdate = "1JAN2018"d to "1JAN2019"d by 14;
	PolicyNumber + 1;
	output;
end;
run;


proc sql;
create table work.want as
select have.*
from work.have
where randomdate lt today()
having max(PolicyNumber) = PolicyNumber
;
quit;

Yes this is still doing some sorting behind the scenes but if you are using the pass-through facility it might be easier on your machine. If this does not work then you would have to look at the data itself and why it cannot be sorted. Maybe post some specifics and a more experienced programmer could help you out.