sria sria - 2 months ago 10
SQL Question

SAS PROC SQL - issue with Left join on two character variables

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.

Joe Joe
Answer

14.14.50 ne 14:14:50

Fix your formats, or use INPUT to make them both a number.