Match a period to a date

[Onizuka]

Hello everybody,

(sorry for my english)

I am trying to add a date depending of a period.

Here an example of my datas :

Data have ;
format cniv1_cniv2 $11. period 2. nbper 8. ;
input cniv1_cniv2 period nbper;
cards;
10000_20000 4 26
30000_40000 9 34
50000_60000 2 32
70000_80000 1 17
;
run ;

I would like to add the last date of the period on a new variable "last_dt_period".

I have calculated the periods on another programm earlier :

Period 1 = 01/03/2019 --> 31/03/2019

Period 2 = 01/02/2019 --> 28/02/2019

Period 3 = 01/01/2019 --> 31/01/2019

....

Period 36 = 01/03/2016 --> 31/03/2016

So what i want to do now, is add in my last table (have), the last date of each period to have something like this :

Data want ;
format cniv1_cniv2 $11. period 2. nbper 2. last_dt_period $10.;
input cniv1_cniv2 period nbper last_dt_period;
cards;
10000_20000 4 26 31/12/2018
30000_40000 9 34 31/07/2018
50000_60000 2 32 28/02/2019
70000_80000 1 17 31/03/2019
;
run ;

How can I do this ?

Thank you very much and good afternoon !

Onizuka

[Jagadishkatam · Accepted solution]

Consider that you have  a period dataset with period variable and start and end date, then you can merge that dataset with main dataset on period then the end date from period could be considered as last period date

Data have ;
format cniv1_cniv2 $11. period 2. nbper 8. ;
input cniv1_cniv2 period nbper;
cards;
10000_20000 4 26
30000_40000 9 34
50000_60000 2 32
70000_80000 1 17
;
run ;


data period;
input period start :ddmmyy10. end : ddmmyy10.;
cards;
1 01/03/2019 31/03/2019
2 01/02/2019 28/02/2019
3 01/01/2019 31/01/2019
;

proc sort data=have;
by period;
run;


proc sort data=period;
by period;
run;


data want;
merge have(in=a) period(in=b);
by period;
if a;
format end date9.;
run;

[Onizuka]

Thank you for your answer.

The problem is that i don't have a table with a start and end date.

This is where I'm calculating my periods :

%macro calcul_periode ;

%local j ;
%DO j = 1 %TO 3 ; /* 3 times because i have 3 files */

	data mca.periode_niv&j (drop = date_fin_trim) ; 
	set mca.res_niv&j ;

		Format periode 2. ;
		date_fin_trim = "31MAR2019"d ; 

		if intnx('month', date_fin_trim, 0) <= date <= date_fin_trim then Periode = 1 ;
		%local i ;
		%DO i = 1 %TO 35 ; 

			if intnx('month', date_fin_trim, %eval(-(&i)-1)) <= date < intnx('month', date_fin_trim, -&i) then Periode = %eval((&i)+1) ;

		%end ;

	run ;

%end ;

%mend calcul_periode ;

[Jagadishkatam]

Are these periods generated in a dataset, are there one separate dataset per period

[Onizuka]

Okey, i have generated this table period, like this :

Data mca.Periode ;

  set mca.periode_niv1 (keep = date periode) ;

      where periode ne . ; /* if period > 36 then periode = .  */

      by periode ;

      if first.periode ;

run ;

Thank you, i will try the merge but I don't think there will be problems !

I come back after to close the topic 🙂

Thank you @Jagadishkatam