topic How to get SAS to import MS xlsx files with date fields as all numeric (or all character) in SAS Programming

How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

jcis7 — Tue, 20 May 2014 21:55:33 GMT

Hello. Any help you can give is much appreciated.

I've successfully imported the 40 xlslx spreadsheets using the code I found on this communities website - program attached.

But, I'm unable to append them because I'm getting error messages that some variables are both character and numeric.

This happens with the date fields (formatted as short date in xlsx) which have some missing observations.

How do I get around this? MIXED=YES gives me more date fields that come up as character than numeric.

Is it better to import as character or as numeric?

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

Reeza — Tue, 20 May 2014 22:04:45 GMT

Your code was truncated.

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

ballardw — Tue, 20 May 2014 22:13:48 GMT

And you probably want to start with the code you used to import the data.

The quickest fix might end up making changes in the Excel data or exporting the data to CSV and reading that with more control than proc import gives, such as control over whether a variable is numeric or character, associated formats and use of appropriate informats.

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

jcis7 — Tue, 20 May 2014 23:22:59 GMT

I highlighted all the date columns in each Excel spreadsheet and changed them to 'short date' but still unable to import the fields (esp ones with more missing values) as numeric.

I can try to export into CSV and import into SAS unless you suggest anything else?

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

jcis7 — Tue, 20 May 2014 23:23:32 GMT

Thanks! Uploaded SAS file because it wasn't pasting properly.

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

stat_sas — Tue, 20 May 2014 23:58:25 GMT

While importing excel files some of the variables may contain missing values in the first row that creates a problem. PROC IMPORT with an Excel file, uses the first eight lines to determine if a variable is numeric or character. So importing csv files will provide more flexibility. You can put GUESSINGROWS = (max number of rows to be examined) option in proc import to determine the exact variable type.

Regards,

Naeem

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

agoldma — Sat, 18 Jun 2016 22:02:51 GMT

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character

Re: How to get SAS to import MS xlsx files with date fields as all numeric (or all character)

agoldma — Sun, 19 Jun 2016 00:08:52 GMT

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character