07-12-2012 02:43 PM
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
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
create table user_information as
select * from test_master
where st_number in(select st_number from test));
07-12-2012 03:18 PM
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.
07-12-2012 03:28 PM
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.
07-12-2012 03:33 PM
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;
proc contents data=test_master;
07-12-2012 03:44 PM
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.
07-12-2012 04:07 PM
I have changed the length and format of test to match with test_master and still getting column as blank
format st_number $20.;
length st_number $ 20;
07-12-2012 04:13 PM
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;
proc freq data=test_master;