topic Re: Merging data of different files by matching date and name in SAS Programming

Merging data of different files by matching date and name

aminkarimid — Mon, 22 May 2017 19:46:13 GMT

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.

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

PaigeMiller — Mon, 22 May 2017 19:31:23 GMT

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?

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

aminkarimid — Mon, 22 May 2017 19:35:34 GMT

How can I do that?

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

PaigeMiller — Mon, 22 May 2017 19:48:39 GMT

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

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

Kurt_Bremser — Mon, 22 May 2017 20:09:52 GMT

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

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

aminkarimid — Mon, 22 May 2017 22:06:39 GMT

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

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

Shmuel — Tue, 23 May 2017 00:19:24 GMT

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.

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

Kurt_Bremser — Tue, 23 May 2017 04:50:31 GMT

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.