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.
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;
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;
@FredrikE great.. that is fantastic. Thank you so much..
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.