BookmarkSubscribeRSS Feed
Abraham
Obsidian | Level 7

Hi Everyone!

 

I want to import data fro emp table from IBM schema into SAS. Data in one of the column (i.e. Empname) in Oracle contain extra space at the end of text. I need to capture those spaces after importing.

 

The problem is that those spaces at the end are automatically ignored by the SAS. Is there any way to change the below code to capture exact data that are present in Oracle table.

 

proc sql;
connect to oracle (user=abc orapw=bcd path=efg);
create table work.emp as
Select * from connection to oracle
(SELECT * FROM IBM.Employee
);

disconnect from oracle;
quit;

8 REPLIES 8
Kurt_Bremser
Super User

Do you mean that columns in SAS have been defined shorter than they were in the DBMS?

If they have the same length, they are always padded with blanks.

What gives you the impression that blanks have been omitted?

If you have doubt, display your names with a HEXw. format, w being double the defined length of the column.

Abraham
Obsidian | Level 7

yes, column in SAS have been defined same length as oracle column.

Column after importing contain same length as in DBMS.

 

E.g suppose the variable in empname contain value as 'Apple  '   (two space at the end).

After importing, SAS capture the same text without any space i.e. 'Apple'

LinusH
Tourmaline | Level 20

Yes, this is the default behaviour in SAS, right or wrong.

This is probably due to the fact that SAS handles a single space as MISSING (similar to NULL in RDBMS).

But does your trailing spaces has business meaning? How do you  intend to use this information?

In most scenarios they arn't being used, hence the SAS default behaviour.

Data never sleeps
Abraham
Obsidian | Level 7

Yes, I need to capture those record for data cleaning. Is there any possibility to capture those record

Kurt_Bremser
Super User

Sorry, but I can't follow you. SAS character variables are always padded with blanks, from the last character to the defined length.

See this:

data test;
length name $10;
name = 'Apple';
testname = put(name,$hex20.);
run;

proc print; run;

Result:

Obs    name           testname

 1     Apple    4170706C652020202020

If you feel that there is something other in your variables but blanks, use the $HEX format to display it.

Astounding
PROC Star

SAS will not track that information for you.  If you look at only SAS and ignore the DBMS side of the equation, consider these statements:

 

if sex='F' then gender='Female';

else gender='Male';

 

In the SAS data set, GENDER has a fixed length of 6.  When GENDER is "Male", SAS actually stores 4 nonblanks plus two blanks.  Any of these later statements would find the same sets of matches:

 

if gender='Male' then do;

if gender='Male ' then do;

if gender='Male  ' then do;

 

When SAS detects that GENDER contains 6 characters, it automatically pads the string on the right with blanks to reach a length of 6 and then checks for equality.

 

SAS never tracks how many blanks were there in the original DBMS value.  It can retrieve the number of nonblanks:

 

L = lengthn(fieldname);

 

If you are certain that L+1 is the original number of characters from the DBMS, you can program upon that assumption.  But the values stored within a SAS character field routinely pad with blanks to reach a fixed length for each field.

LinusH
Tourmaline | Level 20
Record?
The record will not be filtered, it's the column value that will benable trimmed.
What type of cleansing operations will you perform?
Data never sleeps
Ksharp
Super User
Maybe you can use the Oracle function like sas's  lengthm() to get that character variable length.


proc sql;
connect to oracle (user=abc orapw=bcd path=efg);
create table work.emp as
Select * from connection to oracle
(SELECT * ,  lengthm(char_var)  as len  FROM IBM.Employee 
);
disconnect from oracle;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3761 views
  • 0 likes
  • 5 in conversation