BookmarkSubscribeRSS Feed
dash
Obsidian | Level 7

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;

6 REPLIES 6
Reeza
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1248 views
  • 0 likes
  • 2 in conversation