DATA Step, Macro, Functions and more

Matching variables in 2 datasets

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Matching variables in 2 datasets

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


Accepted Solutions
Solution
‎01-11-2017 10:17 PM
Regular Contributor
Regular Contributor
Posts: 160

Re: Matching variables in 2 datasets

Posted in reply to rkdubey84

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,572

Re: Matching variables in 2 datasets

Posted in reply to rkdubey84

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;

Contributor
Posts: 36

Re: Matching variables in 2 datasets

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
Trusted Advisor
Posts: 1,572

Re: Matching variables in 2 datasets

Posted in reply to rkdubey84

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;

Contributor
Posts: 36

Re: Matching variables in 2 datasets

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 Smiley Sad

Thanks @Shmuel .
Trusted Advisor
Posts: 1,572

Re: Matching variables in 2 datasets

Posted in reply to rkdubey84

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 ?

Regular Contributor
Regular Contributor
Posts: 160

Re: Matching variables in 2 datasets

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

Solution
‎01-11-2017 10:17 PM
Regular Contributor
Regular Contributor
Posts: 160

Re: Matching variables in 2 datasets

Posted in reply to rkdubey84

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

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 221 views
  • 5 likes
  • 3 in conversation