Hi Guys,
I currently have 2 different formats of the same data. (Basically they are just dates YYYYMM to me).
1 of them is in the format " LENGTH 6, FORMAT $6, , INFORMAT $6."
The other is in the format "LENGTH 8, FORMAT BEST12. , INFORMAT 12."
I tried to merge my data based on dates, but the log said that they have to be in the same format first.
How do I convert them into the same format? And Which is a better format to use?
Thanks a lot guys!
As Art suggested the first step is to convert all dates to SAS dates. You do not need to do so here, but it is almost always a best practice. Once dates are converted they can be used to merge or join the tables. The formats currently assigned have nothing to do with the problem it is the stored values that you need to deal with. Conside the following simple example of conversion of both numeric and character dates.
data char;
input cdate $6.;
date = mdy(input(substr(cdate,5,2),2.),01,input(substr(cdate,1,4),4.));
datalines;
199101
199202
run;
data num;
input ndate 6.;
date = mdy(mod(ndate,100),01,int(ndate/100));
datalines;
199202
199303
run;
data want;
merge char num;
by date;
run;
proc print data=want;
run;
If you want the SAS date to appear in the YYYYMM format use the YYMMN6. format.
I would create two new fields that contain SAS dates. However, before going any further, does the one with length 8 actually only contain yyyymm or does it also contain the day of the month?
Hi Arthur,
It only contains YYYYMM.
As Art suggested the first step is to convert all dates to SAS dates. You do not need to do so here, but it is almost always a best practice. Once dates are converted they can be used to merge or join the tables. The formats currently assigned have nothing to do with the problem it is the stored values that you need to deal with. Conside the following simple example of conversion of both numeric and character dates.
data char;
input cdate $6.;
date = mdy(input(substr(cdate,5,2),2.),01,input(substr(cdate,1,4),4.));
datalines;
199101
199202
run;
data num;
input ndate 6.;
date = mdy(mod(ndate,100),01,int(ndate/100));
datalines;
199202
199303
run;
data want;
merge char num;
by date;
run;
proc print data=want;
run;
If you want the SAS date to appear in the YYYYMM format use the YYMMN6. format.
Hi.
There's two good reasons for keeping date values in numeric SAS date vars, being calculation and representation.
SAS Dates are required for the use of date functions such as INTNX and DATEDIFF and are the only valid values that are one "format away" from displaying a date in any way you wish.
That said, you could actually perform the join without converting one of the key columns, if and only if, your merge could translate to a SQL join.
Since SQL allow you to match by transformed columns it would just be a matter of finding the right match expression, maybe something like this:
...
on cats(put(SASDATE,year4.),put(SASDATE,month2.)) = cats(CHARDATE)
Cheers from Portugal.
Daniel Santos @ www.cgd.pt
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.