Matching variables in 2 datasets

[rkdubey84]

Dear Friends,

I have 2 Datasets:

1. TradesDataset Containing

Qty Trade_Price   T1 

50   100               9:15 

51   101              9:15

75   102              9:15

65   102              9:15

59   105             9:16

50   100             9:16

56    102            9:16

45    100             9:17

51    101            9:17

2. SpreadDataset Containing

T1    BestBuy  BestSell

9:15   100          101

9:16   101          105

9:17    105          107

I want to Combine the two datasets to Arrive at the following CombineDataset:

Qty Trade_Price   T1   BestBuy  BestSell

50   100               9:15    100          101

51   101              9:15     100          101

75   102              9:15     100          101

65   102              9:15      100          101

59   105             9:16      101         105

50   100             9:16      101         105

56    102            9:16      101         105

45    100             9:17     105          107

51    101            9:17       105          107

How to go about it?

I tried Merge And Set Options but not getting the desired results.

Thanks for helping and sparing your valuable time.

Warm Regards

Ritesh

[Shmuel]

Without testing it, it seems to me that you want:

proc sort data=TradesDataset;  by t1 trade_price; run;

proc sort data=SpreadDataset; by t1 best_but; run;

proc sql;

    create table CombineDataset as select *

    from TradesDataset  as a

    full join SpreadDataset  as b

    on a.t1 = b.t1;

quit;

[rkdubey84]

This Creates Missing values And results in something like this:
Qty Trade_Price T1 BestBuy BestSell
50 100 9:15 . .
51 101 9:15 . .
75 102 9:15 . .
65 102 9:15 . .
. . 9:15 100 101
59 105 9:16 . .
50 100 9:16 . .
56 102 9:16 . .
. . 9:16 101 105
45 100 9:17 . .
51 101 9:17 . .
. . 9:17 105 107

[Shmuel]

Sorry, I have limitted experience with sql;

Please try:

proc sql;

    create table CombineDataset as select *

    from TradesDataset  as a

    /* full */ join SpreadDataset  as b

    on a.t1 = b.t1;

quit;

If it does not help try:

proc sql;

    create table CombineDataset as select *

    from TradesDataset  as a

    inner join SpreadDataset  as b

    on a.t1 = b.t1;

quit;

[rkdubey84]

Really Appreciate your efforts....

Both Join & Inner Join Result in:

WARNING: Variable T1 already exists on file WORK.COMBINEDATASET.
NOTE: Table WORK.COMBINEDATASET created, with 0 rows and 17 columns.

My experience with SQL is limited as well 😞

Thanks @Shmuel .

[Shmuel]

My first answer was righ except a typo:

proc sort data=TradesDataset;  by t1 trade_price; run;

proc sort data=SpreadDataset; by t1 bestbuy; run;

proc sql;

    create table CombineDataset as select *

    from TradesDataset  as a

    full join SpreadDataset  as b

    on a.t1 = b.t1;

quit;

I have tested it and it gives the right wanted results.

Have you checked your log for syntax errors ?

[HB]

As far as I know, the PROC SORT isn't needed for PROC SQL.  Otherwise I agree with this as I posted below.

[HB · Accepted solution]

If I do:

data tradesdataset;
   input Qty Trade_Price  T1 time8.;
   datalines;
50	100	9:15 
51	101	9:15
75	102	9:15
65	102	9:15
59	105	9:16
50	100	9:16
56	102	9:16
45	100	9:17
51	101	9:17
;

data spreaddataset;
	input T1 time8.  BestBuy  BestSell;
	datalines;
9:15	100	101
9:16	101	105
9:17	105	107
;

and then just as Shmuel suggests do:

proc sql;
create table CombineDataset as
select *
    from tradesdataset a
    inner join spreaddataset b
    on a.t1 = b.t1;
quit;

I get the warning as well, but I also get the dataset:

Qty	Trade_Price	T1	BestBuy	BestSell
50	100	33300	100	101
51	101	33300	100	101
75	102	33300	100	101
65	102	33300	100	101
59	105	33360	101	105
50	100	33360	101	105
56	102	33360	101	105
45	100	33420	105	107
51	101	33420	105	107
 

 which seems to be more like what you want. You might need to play with the time format.