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
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.
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;
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;
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 ?
As far as I know, the PROC SORT isn't needed for PROC SQL. Otherwise I agree with this as I posted below.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.