topic Re: Merge with different date format in SAS Programming

Merge with different date format

Xinhui — Wed, 27 Nov 2019 21:01:41 GMT

Hi There

I'm trying to merge two files by "date"

but "date" has different format in two files

the format of "date" in file T2 is BEST12.

the format of "date" in file SP2 is YYMMDDN8.

in this case how can I merge two files by "date"

this is the code I used

DATA master;
MERGE T2 (in=a)
sp2 (in=b);
by date;
if A;
run;

Many Thanks in advance

Re: Merge with different date format

PaigeMiller — Wed, 27 Nov 2019 21:12:06 GMT

Formats do not affect merge. The merge will be done using the unformatted values.

So, are the unformatted values the same so that the data sets can merge properly together???

Re: Merge with different date format

unison — Wed, 27 Nov 2019 21:49:29 GMT

@Xinhui , I think you'll need to get the dates into an actual date format. I suspect your date in t2 is a character of the form "2018/10/01".

data t2_new;
/* set t2; */
/* date_new = input(compress(date,"/"),yymmdd8.); */
date_new = input(compress("2018/10/01","/"),yymmdd8.);
var_from_t2="Hi from T2!";
format date_new yymmddn8.;
run;

data sp2_new;
/* set sp2; */
/* date_new = input("20181001",yymmdd8.); */
date_new = input("20181001",yymmdd8.);
var_from_sp2="Hi from SP2!";
format date_new yymmddn8.;
run;

proc sort data=t2_new; by date_new; run;
proc sort data=sp2_new; by date_new; run;

data want;
merge t2_new(in=a) sp2_new(in=b);
by date_new;
if a;
run;

proc print data=want;
run;

As @PaigeMiller mentioned, if both variables are SAS dates, it won't matter that they differ in display format.

-unison

Re: Merge with different date format

PaigeMiller — Wed, 27 Nov 2019 21:38:07 GMT

Or one of the dates is a numeric like 20191210, which won't merge with dates formatted as MMDDYYN8.

Re: Merge with different date format

Xinhui — Wed, 27 Nov 2019 21:46:25 GMT

no both of "date" are present as 20180101 in the file

Re: Merge with different date format

unison — Wed, 27 Nov 2019 21:50:31 GMT

Thank you @PaigeMiller, I cleaned up my response the "numeric" was misleading!

Re: Merge with different date format

Tom — Wed, 27 Nov 2019 21:51:25 GMT

SAS will merge by the actual values stored in the variables, not by how you have told it to display the values.

So if DATE in SP2 has today's date then the value will be 21,880 it will be displayed as

20191127

And if DATE in T2 also have the value 21,880 then it will be displayed as:

but the values will match.

However if DATE in T2 has the value 20,191,127 has the it will be displayed as

20191127

Which looks a lot like how the 21,880 that as stored in SP2 looks, but the value is almost 1,000 times larger.

You probably want to convert the values in T2 into actual dates. So assuming that you have values like 20,191,127 then you can fix them by converting them to characters string, converting the string into a date value. While you are at it attach a date format so they look like dates when printed. You probably will want to use a format like DATE9 or YYMMDD10 instead of the confusing YYMMDDN8 format that makes the displayed value look like a normal number instead of date.

data t2_fixed;
  set t2;
  date=input(put(date,8.).yymmdd8.);
  format date date9.;
run;

Then they will merge.

data master;
  merge t2_fixed (in=in1) sp2 (in=in2);
  by date;
  if in1;
run;

Re: Merge with different date format

PaigeMiller — Wed, 27 Nov 2019 21:52:31 GMT

@Xinhui wrote:

no both of "date" are present as 20180101 in the file

the format of "date" in file SP2 is YYMMDDN8.

These two statements are contradictory. 20180101 cannot be merged with a date value formatted as YYMMDDN8. because the unformatted dates will not match.

If that's the case, then you have to convert 20180101 into a date that SAS recognizes, as it is not a SAS date.

So ... instead of me posting code, I see that @Tom has already done this.