Export from SAS EG 4.1 to Excel spaces at the end of the text string bug

[nicnad]

Hi,

I am using SAS EG 4.1

I am new to SQL and EG 4.1

I use a sql query like the following to create a sas tableset :

PROC SQL;

CREATE TABLE AQ.TEST

SELECT

TRIM(CUSTOMERS.CUSTOMER_NAME) as CUSTOMER_NAME

FROM TEST.CUSTOMERS AS CUSTOMERS

;QUIT;

The resulting tableset is a clean column with no space, but for some reason, when I use left click export to Excel, the table exported to excel now has a lot of spaces at the end of the customer name.

Does anyone know how to solve this?

Thank you for your help and time.

[TomKari]

Part 1 of your problem is that SAS doesn't have a variable-length CHARACTER data type, so all of your CUSTOMER_NAME records will be padded out with blanks to the full length of the column (you can check this length with right-click Properties against the table).

What you need is a way to get SAS to strip the trailing blanks when exporting to Excel. That part I don't know.

Tom

[TomKari]

I did a little more testing, and I can't replicate your problem. When I "right-click export" to Excel, my 50 character column doesn't have trailing blanks.

Tom

[nicnad]

But your first answer pointed out the problem.

When I right-click Properties against the table TEST, I see that the column CUSTOMER_NAME is length 1000 and format $1000.0 (this must not be right since I want this column to be varchar type) and when I use LEN() function in Excel I see that the cell is 1000 characters long.

How do I set my variable properly in my SQL query in order for it the be the correct length?

Thank you for your help and time

[TomKari]

There is no varchar type in SAS. I'm guessing that your Oracle column is VARCHAR(1000), so SAS has to create a CHAR variable of length 1000 to hold possible long values.

But I believe you should be able to get a trimmed version into Excel, because that's what I get.

I can't try your situation because I have EG 5.1. My two thoughts:

1. This is a hail mary, and I don't think will work, but try it. Run this code:

data AQ.TEST2;

set AQ.TEST;

format CUSTOMER_NAME;

run;

and then export TEST2 to Excel the same way you exported TEST. All this code does is remove the format from the column definition.

2. Assuming that doesn't work, put in a case to SAS Tech Support. They have the ability to run different versions of SAS, so they should be able to duplicate your situation and find a solution.

Tom

[D_Z_]

I was just informed we are moving to SAS 5.1 very shortly... i have been given a copy of the new version to test.  Is there a way in 5.1 to perform this task?  WOOHOO by the way... can't wait for 5.1... so many more features. 

[TomKari]

Hi, D.Z.

Yes, 5.1 is a BIG new release! Check out some of the great new features in the 5.1 Help "What's new in 5.1".

There are a couple of really easy ways to send your SAS data to Excel in 5.1:

1. With your dataset open in the viewer, click Export | Export datasetname, and change the type of file from SAS to Excel. In the Process Flow view, just right click on a dataset and do the same.

2. With your dataset open in the viewer, click Send To | Microsoft Excel, and the dataset will open in Excel. Same works with a right-click in the Process Flow view.

Enjoy!

  Tom

[D_Z_]

I think maybe I didn't clarify enough of what my end goal was.  I am attempting to make a clean dataset with one row holding one observation  instead of 6 rows holding one observation.  Right now, the dataset is useless to me once imported as has data for one observation spanning into 6 rows.  I am not interested in putting the data into excel, as I need to make further queries with this data with other existing DB tables.

Thanks

[Tom]

You probably should open a new discussion thread where you can explain the structure of your data and what you mean by observations spanning rows.

[nicnad]

Thank you for the reply.

This code didn't work either :

data AQ.TEST2;

set AQ.TEST;

format CUSTOMER_NAME;

run;

What is the easiest way to submit a case to SAS tech support and how do I know if my license include support?

[Reeza]

At the bottom of this page, grey footer area, in tiny print is submit a problem report, that's the easiest way to submit a case to Tech support.

AFAIK all license include support but I suppose it may have changed.

[nicnad]

Thank you,

I just submitted a problem report and will keep you posted with the answer.

Best regards,

nicnad

[Grinvydas]

Hi,

data test;

format A $2000. B $10.;

A='bcd';

B=left(trim(A));

run;

After exporting this table to xls i got same results. (EG version 4.1)

Column A has "abc + trailing spaces"

Column B has no trailing spaces.

When exporting to text file i get no trailing spaces in A and B columns.

Somehow this result depends on length of a character column.