Proc import excel file in UNIX SAS

Reply
Occasional Contributor
Posts: 10

Proc import excel file in UNIX SAS

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

Super User
Posts: 18,984

Re: Proc import excel file in UNIX SAS

Not really. 

 

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

Super User
Posts: 7,371

Re: Proc import excel file in UNIX SAS

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: Proc import excel file in UNIX SAS

XLSX

Super User
Super User
Posts: 7,668

Re: Proc import excel file in UNIX SAS

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.

Occasional Contributor
Posts: 10

Re: Proc import excel file in UNIX SAS

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

Super User
Posts: 5,375

Re: Proc import excel file in UNIX SAS

@RW9 I assume that @SB12 is using a PC Files Server, right?
Data never sleeps
Occasional Contributor
Posts: 10

Re: Proc import excel file in UNIX SAS

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

Super User
Super User
Posts: 7,668

Re: Proc import excel file in UNIX SAS

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.  

Ask a Question
Discussion stats
  • 8 replies
  • 545 views
  • 1 like
  • 5 in conversation