10-31-2016 10:03 AM
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.
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;
10-31-2016 10:08 AM
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.
10-31-2016 10:16 AM - edited 10-31-2016 10:17 AM
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'
10-31-2016 10:21 AM
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.
10-31-2016 10:33 AM
Sorry, but I can't follow you. SAS character variables are always padded with blanks, from the last character to the defined length.
data test; length name $10; name = 'Apple'; testname = put(name,$hex20.); run; proc print; run;
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.
10-31-2016 10:46 AM
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';
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.
11-01-2016 12:36 AM
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;