Length issue while Importing XLSX file in Unix SAS

[SPP]

Hi,

I have used the import procedure to create SAS dataset from excel workbook (.xlsx) file in UNIX.

The issue i have is, all the character values in the dataset are assigned to the length of the maximum value in that particular variable. (Values with leading spaces. For. Eg. if the maximum length of variable value is 60, the value such as "ELDER" in same variable is also assigned to length of 60 which should have the length as 5).

I tried all possible character functions to remove the leading spaces from the value however it din't work.

I tried to reverse the string and used substr function, this worked but few values had numbers in between the text so that was not giving me an appropriate answer.

Is there something i am missing in import procedure?

Below is the sample code i have used to import the XLSX file.

proc import datafile="test.xlsx" out=tst dbms=xlsx replace;

getnames=yes;
range="Values$B2:K34";
run;

The only solution i have is to convert the xlsx file to CSV and import the same but I just need to know the solution for XLSX file?

regards,

SPP

[LinusH]

For starters, CSV it's a much more stable format for data interchange than Excel - but what's best to use varies from site to site.

Usually (not always) SAS removes leading blanks when you do assignments. if you want to be sure, use left() function.

If that doesn't help, verify that you are dealing with standard blank values, and not some other non visual character.

[RW9]

Totally agree.  CSV is a far better method of data transfer.  Also note that Office is a Windows product and I wouldn't trust it to work correctly on other OS's.  Go plain text.

[ballardw]

---

@SPP wrote:

I tried all possible character functions to remove the leading spaces from the value however it din't work.

I tried to reverse the string and used substr function, this worked but few values had numbers in between the text so that was not giving me an appropriate answer.

---

You could use syntax similar to:

data have;
   set have;
   variable=strip(variable);
run;

If there are still "leading blanks" in the variable then they are likely some other non-printable character.

[SPP]

I tired using the modifier in Compress function which helped me to get rid of the leading blanks.

variable=compress(variable, , 'c');

Thank you all for your suggestions on my question.

[Ksharp]

I am not sure I understand your question. 

Unlike other DataBase product, SAS don't have VARCHAR(60) data type. All the value of one variable uniform the same length.

scantext=no;   will avoid to use the maximize length of variables for other variables , But I don't not if it is worked for you .