BookmarkSubscribeRSS Feed
SB12
Calcite | Level 5

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

8 REPLIES 8
Reeza
Super User

Not really. 

 

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

SB12
Calcite | Level 5

XLSX

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SB12
Calcite | Level 5

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

LinusH
Tourmaline | Level 20
@RW9 I assume that @SB12 is using a PC Files Server, right?
Data never sleeps
SB12
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1808 views
  • 1 like
  • 5 in conversation