DataSet: Test1
Name Type Length Format Informat
RowID Numeric 8 6. 6.
COL2 Character 6 $6. $6.
COL3 Numeric 8 NUMERIC12. NUMERIC12.
DATE Numeric 8 11. 11.
TIME Character 8 $CHAR8. $CHAR8.
Amount Numeric 8
DataSet: Test2
Name Type Length Format Informat
RowID Numeric 8 9. 9.
COL2 Character 32 $32. $32.
COL3 Date 8 DATETIME27.6 DATETIME27.6
COL4 Character 17 $17. $17.
TIME Character 8 $CHAR8. $CHAR8.
COL5 Numeric 8 NUMERIC12. NUMERIC12.
AMOUNT Numeric 8
Sample Data
Test1
RowID COL2 COL3 DATE TIME AMOUNT
3330 123456 123 20110523 14.14.50 2.00
3330 334567 123 20110523 19.13.34 2.00
3330 889789 123 20110523 20.01.11 2.00
3330 45678 1643 20110523 06.53.05 6.00
TEST2
RowID COL2 COL3 COL4 TIME COL5 Amount
3330 0010181002233611096xyBC3TLnDkVB7 23MAY2011:19:14:50.000000 20110523 14:14:50 14:14:50 123 2.00
3330 0010181005005029491mnopqrbT2cySA 24MAY2011:00:13:34.000000 20110523 19:13:34 19:13:34 123 2.00
3330 001018100222213220332ghijkl63BR1 23MAY2011:11:53:05.000000 20110523 06:53:05 06:53:05 1643 6.00
3330 00101810021738472682abcdef7vUcte 23MAY2011:13:30:03.000000 20110523 08:30:03 06:53:05 5575 1.00
I want to left join Test1 with Test2 on Columns Test1.COL3=Test2.COL5 and Test1.Time=Test2.TIME with the desired output to look like
Final
RowID COL2 COL3 DATE TIME AMOUNT RowID COL2 COL3 COL4 TIME COL5 Amount
3330 123456 123 20110523 14.14.50 2.00 3330 0010181002233611096xyBC3TLnDkVB7 23MAY2011:19:14:50.000000 20110523 14:14:50 14:14:50 123 2.00
3330 334567 123 20110523 19.13.34 2.00 3330 0010181005005029491mnopqrbT2cySA 24MAY2011:00:13:34.000000 20110523 19:13:34 19:13:34 123 2.00
3330 889789 123 20110523 20.01.11 2.00 . . . . . . .
3330 45678 1643 20110523 06.53.05 6.00 3330 001018100222213220332ghijkl63BR1 23MAY2011:11:53:05.000000 20110523 06:53:05 06:53:05 1643 6.00
I'm running the below code is SAS
proc sql;
create table final as
select * from
(
(select * from Test1) A
left join
(select * from Test2) B
on Test1.COL3=Test2.COL5 and Test1.Time=Test2.TIME
)
quit;
and Im not getting the desired output even though TIME column in both the datasets are of same length, Format and Informat
I'm getting the results as
Final
RowID COL2 COL3 DATE TIME AMOUNT RowID COL2 COL3 COL4 TIME COL5 Amount
3330 123456 123 20110523 14.14.50 2.00 . . . . . . .
3330 334567 123 20110523 19.13.34 2.00 . . . . . . .
3330 889789 123 20110523 20.01.11 2.00 . . . . . . .
3330 45678 1643 20110523 06.53.05 6.00 . . . . . . .
I do not understand what is wrong.
14.14.50 ne 14:14:50
Fix your formats, or use INPUT to make them both a number.