topic Re: Matching variables in 2 datasets in SAS Programming

Matching variables in 2 datasets

rkdubey84 — Wed, 11 Jan 2017 19:38:13 GMT

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

Re: Matching variables in 2 datasets

Shmuel — Wed, 11 Jan 2017 20:21:13 GMT

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;

Re: Matching variables in 2 datasets

rkdubey84 — Wed, 11 Jan 2017 20:33:40 GMT

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

Re: Matching variables in 2 datasets

Shmuel — Wed, 11 Jan 2017 20:54:01 GMT

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;

Re: Matching variables in 2 datasets

rkdubey84 — Wed, 11 Jan 2017 21:17:38 GMT

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 .

Re: Matching variables in 2 datasets

Shmuel — Wed, 11 Jan 2017 21:43:55 GMT

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 ?

Re: Matching variables in 2 datasets

HB — Wed, 11 Jan 2017 21:46:32 GMT

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.

Re: Matching variables in 2 datasets

HB — Wed, 11 Jan 2017 21:48:39 GMT

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