01-04-2013 02:58 PM
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 :
CREATE TABLE AQ.TEST
TRIM(CUSTOMERS.CUSTOMER_NAME) as CUSTOMER_NAME
FROM TEST.CUSTOMERS AS CUSTOMERS
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.
01-04-2013 03:05 PM
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.
01-04-2013 03:42 PM
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.
01-04-2013 03:48 PM
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
01-04-2013 09:12 PM
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:
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.
01-07-2013 03:54 PM
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.
01-07-2013 06:04 PM
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.
01-07-2013 06:27 PM
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.
01-08-2013 08:39 AM
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.
01-08-2013 04:19 PM
Thank you for the reply.
This code didn't work either :
What is the easiest way to submit a case to SAS tech support and how do I know if my license include support?
01-08-2013 04:29 PM
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.
01-08-2013 05:08 PM
I just submitted a problem report and will keep you posted with the answer.
09-24-2013 07:14 AM
format A $2000. B $10.;
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.