SAS monthly and daily Date formatting

[Saba1]

I have two data sets with Date columns. One dataset has monthly dates (dataset: have2) i.e. 12/31/2015 (format MMDDYY10.), while the other one is having daily dates (dataset:have1) i.e. 11/26/2010 (MMDDYY10.). I want to create a new dataset by keeping all the observations of "have1" and extracting values of one column from "have2" where ID and Dates of both the datasets are matched. For this purpose, I am trying to convert the format of both the dates to only month and year like "201011", but it doesnot match with daily date observations in "have1" and extracts only those values where the daily date is "month end" (like 10/31/2008 in a sample below). 

I tried following coding to convert dates:

data new;

set have;

date_new=date
FORMAT date_new yymmn6.;

run;

Below is the sample of my datasets and required output.

data have1;
input id date_daily ret;
datalines;
40 3/3/2005  -0.30
40 6/23/2010  0.28 
45 12/15/2015 -0.08
48 10/31/2008 -0.01
48 7/7/2007    0.78
48 1/20/2013   0.54 ;
run;
data have2;
input id date_month value;
datalines;
40 3/31/2005  12
40 4/30/2005  7
40 8/31/2009  10
40 6/30/2010  20
45 10/31/2015 40
45 11/30/2015 42
45 12/31/2015 24
48 10/31/2008 18
48 11/30/2008 45
48 1/20/2013  33
48 7/31/2006  12
48 7/31/2007  10
48 1/31/2013  29 ;
run;
data want;
input id date ret value;
datalines;
40 200503 -0.30 12
40 201006  0.28 20
45 201512 -0.08 24
48 200810 -0.01 18
48 200707  0.78 10
48 201301  0.54 29 ; run;

Please guide me in this regard. Thanks.

[Saba1]

I have two data sets with Date columns. One dataset has monthly dates (dataset: have2) i.e. 12/31/2015 (format MMDDYY10.), while the other one is having daily dates (dataset:have1) i.e. 11/26/2010 (MMDDYY10.). I want to create a new dataset by keeping all the observations of "have1" and extracting values of one column from "have2" where ID and Dates of both the datasets are matched. For this purpose, I am trying to convert the format of both the dates to only month and year like "201011", but it doesnot match with daily date observations in "have1" and extracts only those values where the daily date is "month end" (like 10/31/2008 in a sample below). 

I tried following coding to convert dates:

data new;

set have;

date_new=date
FORMAT date_new yymmn6.;

run;

Below is the sample of my datasets and required output.

data have1;
input id date_daily ret;
datalines;
40 3/3/2005  -0.30
40 6/23/2010  0.28 
45 12/15/2015 -0.08
48 10/31/2008 -0.01
48 7/7/2007    0.78
48 1/20/2013   0.54 ;
run;
data have2;
input id date_month value;
datalines;
40 3/31/2005  12
40 4/30/2005  7
40 8/31/2009  10
40 6/30/2010  20
45 10/31/2015 40
45 11/30/2015 42
45 12/31/2015 24
48 10/31/2008 18
48 11/30/2008 45
48 1/20/2013  33
48 7/31/2006  12
48 7/31/2007  10
48 1/31/2013  29 ;
run;
data want;
input id date ret value;
datalines;
40 200503 -0.30 12
40 201006  0.28 20
45 201512 -0.08 24
48 200810 -0.01 18
48 200707  0.78 10
48 201301  0.54 29 ; run;

Please guide me in this regard. Thanks.

[FredrikE · Accepted solution]

Superfunction INTNX will help you 🙂

data have1;

length id date_daily ret date_month 8;

informat date_daily anydtdte.;

format date_daily yymmdd10. date_month yymmn6.;

input id date_daily ret;

date_month = intnx('month',date_daily,0,'end');

datalines;

40 3/3/2005 -0.30

40 6/23/2010 0.28

45 12/15/2015 -0.08

48 10/31/2008 -0.01

48 7/7/2007 0.78

48 1/20/2013 0.54

;

run;

data have2;

length id date_month value 8;

informat date_month anydtdte.;

format date_month yymmn6.;

input id date_month value;

datalines;

40 3/31/2005 12

40 4/30/2005 7

40 8/31/2009 10

40 6/30/2010 20

45 10/31/2015 40

45 11/30/2015 42

45 12/31/2015 24

48 10/31/2008 18

48 11/30/2008 45

48 1/20/2013 33

48 7/31/2006 12

48 7/31/2007 10

48 1/31/2013 29

;

run;

proc sql;

create table want as

select a.id, a.date_month,a.ret, b.value from have1 as a

left join have2 as b

on a.id = b.id

and a.date_month=b.date_month

;

quit;

[Saba1]

@FredrikE great.. that is fantastic. Thank you so much..