topic Re: Proc import excel file in UNIX SAS in SAS Programming

Proc import excel file in UNIX SAS

SB12 — Mon, 29 Feb 2016 01:26:12 GMT

When i use proc import to import the excel file in unix SAS, some columns with numeric values are imported as dollar format. However, when i use proc import to import the same excel file in PC SAS, the column values comes in as standard numeric type with no format. Is there any option i can use in proc import in Unix SAS so that the numeric values are not imported as dollar format.

Thank you

Re: Proc import excel file in UNIX SAS

Reeza — Mon, 29 Feb 2016 02:26:06 GMT

Not really.

Check the format/informat. It's relatively easy to strip the format off if you don't like it.

Re: Proc import excel file in UNIX SAS

Kurt_Bremser — Mon, 29 Feb 2016 07:44:50 GMT

What file format are you using for the transfer (XLSX, CSV)?

Re: Proc import excel file in UNIX SAS

RW9 — Mon, 29 Feb 2016 09:28:41 GMT

Are you sure your trying to import an Excel file (i.e. an XLS or XLSX file) on Unix, I thought as Excel was Windows only, the engine to import directly was not available on Unix? Anyways, I would strongly recommend to save the file as CSV, and write a datastep import program - Excel is notoriously unstructured, and combine that with the guessing of proc import is more headaches than just typing a program in.

Re: Proc import excel file in UNIX SAS

LinusH — Mon, 29 Feb 2016 19:12:33 GMT

@RW9 I assume that @SB12 is using a PC Files Server, right?

Re: Proc import excel file in UNIX SAS

SB12 — Mon, 29 Feb 2016 19:31:49 GMT

XLSX

Re: Proc import excel file in UNIX SAS

SB12 — Mon, 29 Feb 2016 23:01:33 GMT

Yes, proc import with the dbms=xlsx works in Unix.

Re: Proc import excel file in UNIX SAS

SB12 — Mon, 29 Feb 2016 23:04:29 GMT

I was not using it before. I was using standard proc import with dbms=xlsx. The import would be done however some of the numeric columns would come in dollar format. Now i tried with the PC SAS file server and it worked for me. The numeric columns are in standard numeric type that i was looking for and not in dollar format. Thanks

Re: Proc import excel file in UNIX SAS

RW9 — Tue, 01 Mar 2016 09:29:45 GMT

So it definately is an Excel file then. Ok, well to be honest, my advice does not really change much anyhows. Save the file into a text based delimited file format - i.e. a proper data transfer file format - then write a datastep import program. Excel is not structured and proc import is a guessing procedure, these will cause you headaches. Importing a simple text file is very simple and you have full control over what columns are imported, what type they are etc.