BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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.

12 REPLIES 12
TomKari
Onyx | Level 15

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
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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_
Obsidian | Level 7

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.  Smiley Happy

TomKari
Onyx | Level 15

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_
Obsidian | Level 7

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Thank you,

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

Best regards,

nicnad

Grinvydas
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2174 views
  • 6 likes
  • 6 in conversation