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
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;
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;
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 ;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.