BookmarkSubscribeRSS Feed
landgraf
Calcite | Level 5

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

10 REPLIES 10
PGStats
Opal | Level 21

$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

PG

PG
landgraf
Calcite | Level 5

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

Reeza
Super User

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.

landgraf
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

landgraf
Calcite | Level 5

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


Tom
Super User Tom
Super User

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;

landgraf
Calcite | Level 5

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

esjackso
Quartz | Level 8

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?

EJ

landgraf
Calcite | Level 5

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

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!

Discussion stats
  • 10 replies
  • 2600 views
  • 0 likes
  • 5 in conversation