how to pick a collection's name if its time range meets a particular year: 2040 in sas

[annypanny]

Hi Community,

suppose there is a collection table as below,

Sc_no  Strategy  Collection_name

A0007   FLEXI    Flexi 2035-37 (BLK)
A0008   FLEXI    itl      2037-39 (BLK)
A0009   FLEXI    cptl   2040-42 (BLK)
A0010   FLEXI    sen   2038-40 (BLK)

I have to calculate the year which is 20 years from run date. E.g if today’s date is 12MAY2020, then this step will give 2040. And it will pick only that collection whose time range meets 2040. Example from the above table, the last two rows i.e., cptl  2040-42 (BLK), sen  2038-40 (BLK) the time ranges meets 2040. then this will be picked up automatically.

How can I do that

Thanks in advance   

[ed_sas_member · Accepted solution]

Hi @annypanny 

Here is a way to do this.

The prxchange function identifies in the year boundaries in the pattern of each collection name.

Best,

data have;
	infile datalines dlm="," dsd;
	input Sc_no $ Strategy $ Collection_name :$20.;
	datalines;
A0007,FLEXI,"Flexi 2035-37 (BLK)"
A0008,FLEXI,"itl      2037-39 (BLK)"
A0009,FLEXI,"cptl   2040-42 (BLK)"
A0010,FLEXI,"sen   2038-40 (BLK)"
;
run;

data want;
	set have;
	_low_bound  = prxchange('s/^.*(\d{2}\d{2})-\d{2}.*$/$1/i',1,Collection_name);
	_high_bound = prxchange('s/^.*(\d{2})\d{2}-(\d{2}).*$/$1$2/i',1,Collection_name);;
	if _low_bound <= year(today())+20 <= _high_bound then output;
	drop _:;
run;

 

 

[annypanny]

working for me, thanks a lot.

[ed_sas_member]

You're welcome ! Thank you @annypanny 

[annypanny]

Hi mam

it is running fine for this type of range 2040-42 but when I used 2040-2042, then the code is not returning anything. Can You help me in this

[ed_sas_member]

Hi @annypanny 

I have adapte the code to take into account this situation:

data have;
	infile datalines dlm="," dsd;
	input Sc_no $ Strategy $ Collection_name :$40.;
	datalines;
A0007,FLEXI,"Flexi 2035-37 (BLK)"
A0008,FLEXI,"itl      2037-39 (BLK)"
A0009,FLEXI,"cptl   2040-42 (BLK)"
A0010,FLEXI,"sen   2038-40 (BLK)"
A0010,FLEXI,"sen   2038-2042 (BLK)"
;
run;

data want;
	set have;
	_low_bound  = prxchange('s/^.*(\d{2}\d{2})-\d{2}.*$/$1/i',1,Collection_name);
	
	/*Second year in format yy e.g. 2038-42 */
	if prxmatch('/^.*(\d{2})\d{2}-(\d{2})\D*$/i',Collection_name) then
		_high_bound = prxchange('s/^.*(\d{2})\d{2}-(\d{2}).*$/$1$2/i',1,Collection_name);

	/*Second year in format yyyy e.g. 2038-2042 */
	else if prxmatch('/^.*(\d{2})\d{2}-(\d{4})\D*$/i',Collection_name) then
		_high_bound = prxchange('s/^.*(\d{2})\d{2}-(\d{4}).*$/$2/i',1,Collection_name);
	
	if _low_bound <= year(today())+20 <= _high_bound then output;
	
	drop _:;
run;

proc print;

Best,

[annypanny]

it is working, thank you