BookmarkSubscribeRSS Feed
totuomin1
Calcite | Level 5

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.

 

 

6 REPLIES 6
FredrikE
Rhodochrosite | Level 12

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;

 

 

totuomin1
Calcite | Level 5

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.

Astounding
PROC Star

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?

totuomin1
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

 

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 942 views
  • 1 like
  • 4 in conversation