topic Re: How to include space in sas in SAS Programming

How to include space in sas

Abraham — Mon, 31 Oct 2016 14:03:16 GMT

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;

Re: How to include space in sas

Kurt_Bremser — Mon, 31 Oct 2016 14:08:04 GMT

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.

Re: How to include space in sas

Abraham — Mon, 31 Oct 2016 14:17:40 GMT

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'

Re: How to include space in sas

LinusH — Mon, 31 Oct 2016 14:21:42 GMT

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.

Re: How to include space in sas

Abraham — Mon, 31 Oct 2016 14:27:31 GMT

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

Re: How to include space in sas

Kurt_Bremser — Mon, 31 Oct 2016 14:33:05 GMT

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.

Re: How to include space in sas

Astounding — Mon, 31 Oct 2016 14:46:17 GMT

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;

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.

Re: How to include space in sas

LinusH — Mon, 31 Oct 2016 16:33:10 GMT

Record?
The record will not be filtered, it's the column value that will benable trimmed.
What type of cleansing operations will you perform?

Re: How to include space in sas

Ksharp — Tue, 01 Nov 2016 04:36:09 GMT

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;