DATA Step, Macro, Functions and more

How to include space in sas

Reply
Contributor
Posts: 54

How to include space in sas

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;

Super User
Posts: 6,928

Re: How to include space in sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 54

Re: How to include space in sas

[ Edited ]

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'

Super User
Posts: 5,254

Re: How to include space in sas

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
Contributor
Posts: 54

Re: How to include space in sas

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

Super User
Posts: 6,928

Re: How to include space in sas

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,071

Re: How to include space in sas

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.

Super User
Posts: 5,254

Re: How to include space in sas

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
Super User
Posts: 9,662

Re: How to include space in sas

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;

Ask a Question
Discussion stats
  • 8 replies
  • 378 views
  • 0 likes
  • 5 in conversation