topic Not getting actual record using proc sql in SAS Programming

Not getting actual record using proc sql

dash — Thu, 12 Jul 2012 18:43:02 GMT

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;

Re: Not getting actual record using proc sql

Reeza — Thu, 12 Jul 2012 19:18:21 GMT

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.

Re: Not getting actual record using proc sql

dash — Thu, 12 Jul 2012 19:28:46 GMT

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.

Re: Not getting actual record using proc sql

Reeza — Thu, 12 Jul 2012 19:33:37 GMT

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;

Re: Not getting actual record using proc sql

dash — Thu, 12 Jul 2012 19:44:33 GMT

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

Re: Not getting actual record using proc sql

dash — Thu, 12 Jul 2012 20:07:59 GMT

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;

Re: Not getting actual record using proc sql

Reeza — Thu, 12 Jul 2012 20:13:47 GMT

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;