Merging data of different files by matching date and name

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 144
Accepted Solution

Merging data of different files by matching date and name

[ Edited ]

Hello everybody;
I have two tables. One of them is in excel and the other one is in access. The excel file has name, date and volume1 variables. The access file has name, date and volume2 variables. I want to merge them by name & date. In other word, I want that SAS find the same date and name, and then merge volume1 & volume 2 variables. There is missing data in the excel file.

How can I do that?
Thank you.


Accepted Solutions
Solution
‎05-25-2017 09:25 AM
Trusted Advisor
Posts: 1,579

Re: Merging data of different files by matching date and name

[ Edited ]
Posted in reply to aminkarimid

Do you have issues with importing the two tables into SAS ?

What variable type are the dates in both tables? are they sas dates or sas datetime? or character type?

 

Suppose no - then sort the tables if need then just merge them:

proc sort data=table01; by date name; run;
proc sort data=table02; by date name; run;

data want;
 merge table01 table02;
   by date name;
run;

If you have more issues post your full log.
 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,931

Re: Merging data of different files by matching date and name

Posted in reply to aminkarimid

I'd have to say it's not clear to me the point of your question.

 

This is a simple MERGE in SAS, do you have a more detailed problem description?

Frequent Contributor
Posts: 144

Re: Merging data of different files by matching date and name

Posted in reply to aminkarimid
How can I do that?
Trusted Advisor
Posts: 1,931

Re: Merging data of different files by matching date and name

Posted in reply to aminkarimid

I am asking for a much more detailed statement of what you want to do AND what you are having a problem with.

Super User
Posts: 7,832

Re: Merging data of different files by matching date and name

Posted in reply to aminkarimid

Post the datasets (as datasteps!) after import, so we can work with real data. Just enough observations to illustrate the issue.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 144

Re: Merging data of different files by matching date and name

Posted in reply to KurtBremser

I have two tables.

Table01:

 

date name time volume1 intraday
24/03/2008 abc 10:00 100 .
24/03/2008 abc 10:00 120 .
24/03/2008 abc 10:00 100 320
24/03/2008 abc 11:00 200 .
24/03/2008 abc 11:00 250 450
24/03/2008 abc 12:30 110 .
24/03/2008 abc 12:30 120 230
24/03/2008 xyz 09:30 250 .
24/03/2008 xyz 09:30 240 .
24/03/2008 xyz 09:30 170 660
24/03/2008 xyz 10:00 180 .
24/03/2008 xyz 10:00 140 .
24/03/2008 xyz 10:00 110 430
24/03/2008 xyz 12:00 90 90
25/03/2008 abc 09:30 110 .
25/03/2008 abc 09:30 210 .
25/03/2008 abc 09:30 100 420
25/03/2008 abc 10:00 100 100
25/03/2008 abc 11:00 210 .
25/03/2008 abc 11:00 220 .
25/03/2008 abc 11:00 260 690
25/03/2008 asdf 11:00 150
25/03/2008 asdf 11:00 100 250
25/03/2008 asdf 12:30 110 .
25/03/2008 asdf 12:30 200 .
25/03/2008 asdf 12:30 260 570
25/03/2008 xyz 09:00 300 300
25/03/2008 xyz 09:30 250 .
25/03/2008 xyz 09:30 170 .
25/03/2008 xyz 09:30 170 590
25/03/2008 xyz 11:30 200 200

 

Table02:

 

date name Trade volume2
24-Mar-2008 abc 10 1000
24-Mar-2008 asd 20 1180
24-Mar-2008 xyz 15 1500
24-Mar-2008 qwe 30 2000
25-Mar-2008 zxc 10 900
25-Mar-2008 abc 15 1210
25-Mar-2008 xyz 25 1090
25-Mar-2008 fgh 15 1340
26-Mar-2008 abc 12 1250

 

I want to merge data such as below:

 

Table03:

 

date name time volume1 intraday volume2
24/03/2008 abc 10:00 100 . .
24/03/2008 abc 10:00 120 . .
24/03/2008 abc 10:00 100 320 .
24/03/2008 abc 11:00 200 . .
24/03/2008 abc 11:00 250 450 .
24/03/2008 abc 12:30 110 . .
24/03/2008 abc 12:30 120 230 1000
24/03/2008 xyz 09:30 250 . .
24/03/2008 xyz 09:30 240 . .
24/03/2008 xyz 09:30 170 660 .
24/03/2008 xyz 10:00 180 . .
24/03/2008 xyz 10:00 140 . .
24/03/2008 xyz 10:00 110 430 .
24/03/2008 xyz 12:00 90 90 1180
25/03/2008 abc 09:30 110 . .
25/03/2008 abc 09:30 210 . .
25/03/2008 abc 09:30 100 420 .
25/03/2008 abc 10:00 100 100 .
25/03/2008 abc 11:00 210 . .
25/03/2008 abc 11:00 220 . .
25/03/2008 abc 11:00 260 690 1210
25/03/2008 xyz 09:00 300 300 .
25/03/2008 xyz 09:30 250 . .
25/03/2008 xyz 09:30 170 . .
25/03/2008 xyz 09:30 170 590 .
25/03/2008 xyz 11:30 200 200 1090

 

How can I make the table03 by merging data?

 

Thank you

 

Solution
‎05-25-2017 09:25 AM
Trusted Advisor
Posts: 1,579

Re: Merging data of different files by matching date and name

[ Edited ]
Posted in reply to aminkarimid

Do you have issues with importing the two tables into SAS ?

What variable type are the dates in both tables? are they sas dates or sas datetime? or character type?

 

Suppose no - then sort the tables if need then just merge them:

proc sort data=table01; by date name; run;
proc sort data=table02; by date name; run;

data want;
 merge table01 table02;
   by date name;
run;

If you have more issues post your full log.
 

Super User
Posts: 7,832

Re: Merging data of different files by matching date and name

Posted in reply to aminkarimid

These are not datasteps that read data from datalines, so we still have no idea what types the variables are and what formats are used.

The Excel file is also useless to convey that information.

PLEASE POST YOUR DATA AS DATASTEPS WITH DATALINES.

I hope that was clear enough now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 189 views
  • 3 likes
  • 4 in conversation