topic Re: Export from SAS EG 4.1 to Excel spaces at the end of the text string bug in SAS Enterprise Guide

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

nicnad — Fri, 04 Jan 2013 19:58:57 GMT

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.

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

TomKari — Fri, 04 Jan 2013 20:05:50 GMT

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

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

TomKari — Fri, 04 Jan 2013 20:42:22 GMT

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

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

nicnad — Fri, 04 Jan 2013 20:48:30 GMT

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

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

TomKari — Sat, 05 Jan 2013 02:12:43 GMT

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

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

D_Z_ — Mon, 07 Jan 2013 20:54:08 GMT

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.

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

TomKari — Mon, 07 Jan 2013 23:04:41 GMT

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

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

D_Z_ — Mon, 07 Jan 2013 23:27:04 GMT

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

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

Tom — Tue, 08 Jan 2013 13:39:11 GMT

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.

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

nicnad — Tue, 08 Jan 2013 21:19:17 GMT

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?

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

Reeza — Tue, 08 Jan 2013 21:29:56 GMT

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.

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

nicnad — Tue, 08 Jan 2013 22:08:07 GMT

Thank you,

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

Best regards,

nicnad

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

Grinvydas — Tue, 24 Sep 2013 11:14:07 GMT

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.