topic Re: Matching data (ID and date) in SAS Programming

Matching data (ID and date)

JKCho — Sun, 07 Jul 2019 21:47:33 GMT

Hello!

I am a new SAS learner and particularly having difficulties in matching multiple datasets. Many classes have taught me various regression analysis but mana trimming...

What I want to do is...

I have two data files. there is a variable in common so that I can match these two files by this variable. However, my date files are panel data.

For example,

Data1 Data2

ID Date(Daily) ID Date(Monthly)

1000 09/09/2000 1000 08/10/2000

1000 09/10/2000 1000 09/10/2000

1000 09/11/2000 2000 as same as others...

and so on.... for ID 1000 and so on....

2000 09/01/2000

2000 same as above

and so on.... for ID 2000

3000

and so on....

What I want to have is... to match by ID as below.

MatchedData

ID Date(Daily) Date(Monthly)

1000 09/09/2000 .(missing data)

1000 09/10/2000 09/10/2000

1000 09/11/2000 .(missing data)

2000 Like the above...

3000

When I used many-to-many merge, this code created a huge data whose date numbers are Date(daily) multiplied by Date(Monthly).

This is not a thing I wish to have but I merely want to know the code for matching.

To say simple, I want to match both ID and date. What is demanding is there might be no available matching ID, date or both. For example, there is ID 1111 in data1 but not in data2, or ID 1111 with date 09/01/2000 but not exactly the same in data2. At this stage, I rather let them be; all unmatch rows are retained.

Please please PLEASE share your wisdom!! I know it is kind of easy but for me so demanding...

Also... if you are generous enough, please help me once again.

I have other data that are time series and want to combine these by 'year'.

One is yearly data and another is Monthly.

What I want to have is...

Matched

Variable1(from Monthly) Variable2(from yearly) Monthly Yearly

0000 aaaa 10/01/2000 2000

0000 aaaa 11/01/2000 2000

0000 aaaa 12/01/2000 2000

0000 bbbb 01/01/2001 2001

Since Variable2 are from Yearly data, there should be many duplicates in Matched and that is what I want. As I need to match by using different variables I do not even know how to start.

I appreciate your time on my questions and thank you for your help very much!!!!

Re: Matching data (ID and date)

ChrisNZ — Sun, 07 Jul 2019 22:42:32 GMT

Welcome the the SAS community!

1. Please provide your data in the form of a data step (and check that the pasted code runs properly) when asking questions, like this:

data DATA_D ;
  input ID DATE : ddmmyy10.; 
  format DATE ddmmyy10.;
cards;
1000 09/09/2000                     
1000 09/10/2000                     
1000 09/11/2000                     
run;

data DATA_M;
  input ID DATE ddmmyy10.; 
  format DATE ddmmyy10.;
cards; 
1000 08/10/2000
1000 09/10/2000
run;

2. This should do what you need:

data WANT;
  merge DATA_D (in=D)
        DATA_M (in=M);
  by ID DATE;
  if D then DATE_D=DATE;
  if M then DATE_M=DATE;
  drop DATE;
  format DATE_D DATE_M ddmmyy10.;
run;

proc print noobs; run;

ID	DATE_D	DATE_M
1000	09/09/2000	.
1000	.	08/10/2000
1000	09/10/2000	09/10/2000
1000	09/11/2000	.

Re: Matching data (ID and date)

Reeza — Mon, 08 Jul 2019 02:56:03 GMT

Please please PLEASE share your wisdom!! I know it is kind of easy but for me so demanding...

The quickest way to get help, is to put in some effort and then show what you've tried. That's usually more than enough to get a decent answer.

Re: Matching data (ID and date)

JKCho — Tue, 09 Jul 2019 18:40:39 GMT

well.. I am actually working on a massive data set but I am too clumsy. I have utilized my codes on the internet but all was not the answer...

maybe it is better to post a new post and attach my code.

Thank you:)