Not getting actual record using proc sql

[dash]

Hello everyone,

I have imported 1 lakh data from Text file using proc import procedure into SAS environment and rename as test dataset (with column name st_number having data available like this LU2009CE2013 etc). Again I have connected to oracle database and export data into sas dataset (test_master) containing variable st_number, user_inf.

dataset   --------->   TEST---- st_number 

                            TEST_MASTER-st_number, user_inf

I want to get the user information of TEST dataset containing strings. But why I am getting only single record in stead of 1 lakh. Can anyone help me

proc sql;

create table user_information as

(

select * from test_master

where st_number in(select st_number from test));

quit;

[Reeza]

what happens when you do a left join instead?

My guess is they're different lengths and then the character fields won't match if one has leading or trailing blanks.

[dash]

Thanks Reeza,

When I am putting left join, then I am getting one column i.e. st_number as blank.

Could you please help me how to solve as I am new.

[Reeza]

run a proc contents on each data set and check the length and format of each variable. If they're different you'll probably need to make them the same.

But I'm just GUESSING. Without the actual data there's no way to actually tell.

proc contents data=test;

run;

proc contents data=test_master;

run;

[dash]

Dear Reeza,

Yes you are right. How can I do.

For test, length  is 17 and format is   $17.

For test_master, length 20  and format is   $20.

Advance thanks

[dash]

Dear Reeza

I have changed the length and format of test to match with test_master and still getting column as blank

data test1;

set test;

format st_number $20.;

run;

data test1;

length st_number $ 20;

set test;

run;

[Reeza]

What should it be, 20 or 17? if its 20 perhaps some of the records were truncated when they were read in.

Running a proc freq on the records should let you see why they don't match.

proc freq data=test;

table st_number;

run;

proc freq data=test_master;

table st_number;

run;