topic Re: Merge two non-matching data sets in SAS Procedures

Merge two non-matching data sets

totuomin1 — Mon, 04 Sep 2017 08:03:59 GMT

Hi, I'm trying to merge two sets by date, but haven't found a solution yet. The dates are from two different years, which is probably the cause of the merging problem. I used DATEPART to extract the date5. format, but it seems the year part is still in there somewhere.

Set 1

date value

1 a

2 b

4 c

6 d

Set 2

date value

1 e

3 f

5 g

6 h

What I want is

date value1 value2

1 a e

2 b .

3 . f

4 c .

5 . g

6 d h

Does anyone know how to solve this issue? Sorry if this is a stupid question, still a SAS newbie.

Re: Merge two non-matching data sets

FredrikE — Mon, 04 Sep 2017 08:08:17 GMT

Regarding the date problem you need to supply more information, 'bout the merge, try the merge 🙂

data one;

length date 8 value $1;

input date value;

datalines;

1 a

2 b

4 c

6 d

;

run;

data two;

length date 8 value $1;

input date value;

datalines;

1 e

3 f

5 g

6 h

;

run;

data tog;

merge one(rename=(value=value1)) two(rename=(value=value2));

by date;

run;

Re: Merge two non-matching data sets

RW9 — Mon, 04 Sep 2017 08:21:04 GMT

Please post your test data in the form of a datastep, and use data which acurately reflects the data you are using. I can tell nothing about structure from what you have posted, nor is the date column reflective of a date. You can use this:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

To get datasets of your exact data. Once we can see what is actually there, then we can advise futher. Do note that formatting a numeric date does not change the underlying number, just how it is represented, so bear that in mind, datepart function returns the date - all of the date including year month and day - from a datetime variable. Valid numeric dates have to have all the parts. If you just want to merge on month and day( and why as that makes no sense) then you need to put the value to a character and use that.

Re: Merge two non-matching data sets

totuomin1 — Mon, 04 Sep 2017 08:22:25 GMT

I used DATEPART combined with date5., and the data is from 2016 and 2017, Jan 01 - Aug 31 in both cases.

However, when I MERGE the two tables BY DATE, I receive

1 a

2 b

4 c

6 d

1 e

3 f

5 g

6 h

In other words, the year part seems to have remained in date despite using the date5. format.

Your example works perfectly, if only I knew how to apply it to my data properly.

Re: Merge two non-matching data sets

Astounding — Mon, 04 Sep 2017 10:54:00 GMT

It sounds like:

Your original date variables are actually date-time values, hence the need to apply DATEPART, and
One data set contains data for 2016, while the other contains data for 2017. You would like to match based on the day of the year.

Does that sound about right?

Re: Merge two non-matching data sets

totuomin1 — Tue, 05 Sep 2017 06:44:38 GMT

That's exactly what I'm trying to do, to no avail so far 🙂

Re: Merge two non-matching data sets

RW9 — Tue, 05 Sep 2017 08:01:25 GMT

This goes to show why providing test data in the form of a datastep and what that test data should ouput is so important, we are still guessing what the data looks like and what you want out. So I have give an example here with test data I made up and to an output I think you want:

data have1;
  date="01JAN2016T12:12:00"dt; value="a"; output;
  date="10FEB2016T02:20:00"dt; value="b"; output;
run;
data have2;
  date="01JAN2017T12:12:00"dt; value="e"; output;
  date="11FEB2017T02:20:00"dt; value="f"; output;
run;
proc sql;
  create table WANT as
  select  coalesce(substr(put(A.DATE,datetime.),1,5),substr(put(B.DATE,datetime.),1,5)) as DATE,
          A.VALUE as VALUE1,
          B.VALUE as VALUE2
  from    HAVE1 A
  full join HAVE2 B
  on      substr(put(A.DATE,datetime.),1,5)=substr(put(B.DATE,datetime.),1,5);
quit;