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;
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.
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'
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.
Yes, I need to capture those record for data cleaning. Is there any possibility to capture those record
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.