topic Re: MS Access error when uploading SAS generated Excel output in Microsoft Integration with SAS

MS Access error when uploading SAS generated Excel output

landgraf — Mon, 09 Sep 2013 17:59:21 GMT

Hello. I am a new SAS user and have written relatively simple SAS programs to extract data into Excel files.

I am using SQL in the SAS program to get data from Oracle.

The data are output to a first SAS file, then to a second SAS file so I can assign the necessary variable names fo uplaod into Access.

I am using a proc export step to get the data into Excel files.

Access is telling me that one or more character fields in the Excel files are too big for the Access table fields.

I have tried to use the TRIM function in a DATA step to get rid of any trailing blanks when creating the second SAS file but all the character fields are still being output as $63.

ANy suggestions would be greatly appreciated . . .

Re: MS Access error when uploading SAS generated Excel output

PGStats — Mon, 09 Sep 2013 18:41:24 GMT

$63. is a format, i.e. the printed representation of your variable. It can be applied to a string of 2000 characters in which case it would display the first 63 characters. The only way to set the length of a string is when it is created or to create a new string and assign it a length.

hth

Re: MS Access error when uploading SAS generated Excel output

landgraf — Mon, 09 Sep 2013 21:09:26 GMT

So how do I make the field smaller so I can upload the data into the Access file?

(The Access field is a text 4.)

Thanks, Anne

Message was edited by: Anne Landgraf

Re: MS Access error when uploading SAS generated Excel output

Reeza — Mon, 09 Sep 2013 21:11:13 GMT

How are you creating your excel file from SAS? What is the length and format of the variable in SAS before the export?

SAS can connect directly to Access if you wanted to skip the Excel step.

Re: MS Access error when uploading SAS generated Excel output

Tom — Mon, 09 Sep 2013 21:40:21 GMT

Excel should not have any issue if your data is really ok.

This sounds to me like you tried to assign a numeric variable to character variable and allowed SAS to auto-convert a number into a character variable. When it does that it will right align the value into the character variable.

Look for note like this in you SAS log :

NOTE: Numeric values have been converted to character values ....

Re: MS Access error when uploading SAS generated Excel output

landgraf — Tue, 10 Sep 2013 13:59:16 GMT

I use a DATA step. The incoming data is char 4, like so: GBPF.

When I do a proc contents on the SAS file that is used to create the Excel file, all the char fields show as at least $63 format with a length of 63.

I tried the TRIM function and the SUBSTR function in the data step, but no change.

Is there a way to specify field types/lengths in the proc export step?

Thanks.

BTW: We have been using Hyperion to do this with no problems so I don't think it's the data.

Re: MS Access error when uploading SAS generated Excel output

landgraf — Tue, 10 Sep 2013 14:10:27 GMT

Hi Tom: No message from SAS about converting data. All char fields are created in SAS as at least 63 in length.

The field in question is char 4 and the data is like so: GBTW.

The data should be fine - is coming from Oracle and we are using Hyperion software to do the deed every day until I get the programs converted to SAS.

Thanks,

Anne

Re: MS Access error when uploading SAS generated Excel output

esjackso — Tue, 10 Sep 2013 14:36:13 GMT

How are you importing the data into Access? Is it a manual load using the import wizards or is it VBA code using a file format specification or other method?

As suggested have you tried skipping excel altogether and loading directly with SAS?

Re: MS Access error when uploading SAS generated Excel output

Tom — Tue, 10 Sep 2013 15:48:24 GMT

I would be surprises if they are what it is causing the trouble, but try removing the formats from the character variables. Because your original data is coming from Oracle the character variables will have unneeded (unwanted) permanently attached formats.

data want;

set have ;

format _character_ ;

run;

Re: MS Access error when uploading SAS generated Excel output

landgraf — Fri, 13 Sep 2013 19:50:06 GMT

Hi: I found out that the $63 and length 63 are coming from Oracle, not SAS.

I talked to someone in another dept here who showed me how to put SAS length and format statements in the SQL "Create Table" section.

Going to try this ASAP!

Thanks,

Anne

Re: MS Access error when uploading SAS generated Excel output

landgraf — Fri, 13 Sep 2013 19:51:51 GMT

Tom: You pegged it.

I spoke with a colleague here who confirmed that the $63 and length 63 are coming from Oracle.

He showed me how tocorrect this in the SQL "Create table" section.

Thanks for your help!

Anne