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.
... View more
