Proc import- from xlsx to SAS/ SAS is omitting observations with a hyphen in them, how to avoid this

[ahhh]

Hi,

I have an xlsx file where one column has numbers and some observations in this column have numbers with hyphen embedded within them. When I import this file into SAS, SAS is omitting the variable values with hyphens in them and only displaying the numbers without hyphens. How do I correct this?

I appreciate your help!

Thanks!

[art297]

If you're using SAS9.4 or newer you could trying using the XLSX engine (i.e., DBMS=XLSX). I think it will automatically treat such fields as being character fields.

Art, CEO, AnalystFinder.com

[error_prone]

Maybe using guessingrow=max helps, but this will increase time required to import the file.

[Reeza]

Guessingrows isn’t a valid option for excel files.

I’m assming in its place you see a period. This is because SAS is expecting a number and numbers don’t have hyphens, so you need to read it in as a character field.

If DBMS=XLSX doesn’t work try DBSASTYPE option.

[error_prone]

"Guessingrows isn’t a valid option for excel files."
Right, but it should be available 😉

[Kurt_Bremser]

Save to csv, import that with a data step, read the column as character. Best method by orders of magnitude.

[ahhh]

I converted into csv file and imported and that hyphen issue got resolved! But another issue came up- the variable with names that had a '/' in them- those variable values got dropped. Any ideas on how to address this??

Example variable name: Ag/ab

it had decimal values 0.5,2.5 etc. They all were displayed as '0' when the import happened 😞

Thank you all in advance! 

Btw DBSASTYPE  did not work..

[Kurt_Bremser]

Take the data step that proc import generated, and adapt it to your needs.

[ballardw]

---

@ahhh wrote:

I converted into csv file and imported and that hyphen issue got resolved! But another issue came up- the variable with names that had a '/' in them- those variable values got dropped. Any ideas on how to address this??

Example variable name: Ag/ab

it had decimal values 0.5,2.5 etc. They all were displayed as '0' when the import happened 😞

Thank you all in advance! 

Btw DBSASTYPE  did not work..

---

Did your "variable names" take up more than one row in the Excel File? The second or subsequent rows would have been treated as data and for a field determined to be numeric a value of ag/ab would not be valid and set to missing. You could look in the csv file using a plain text editor like Notepad or WordPad (NOT EXCEL, it lies about actual content of delimited files sometimes) to see if your Ag/ab value is on row 1 in the text file or not. CSV expects any column headings to use as variable to only occupy the first row even though you can specify data to start on other rows.

You might show the entire log with any messages from the import. Use a code box opened with the {i} forum menu icon to preserve formatting of any of the data diagnostics.

[ahhh]

I used notepad and checked; looks like the variable names were in one row itself and did not extend into second row.

[Kurt_Bremser]

Could you please post the first 5 lines of the file (either as attachment, or into a {i} window)?

[ahhh]

Hi, Here is a sample file- see attachment.. thank you so much

[art297]

I was able to import the file with no problems using:

proc import datafile='/folders/myfolders/import this file.csv'
  out=test;
run;

All of the hyphens, slashes and spaces in the variable names were automatically converted to underscores.

Art, CEO, AnalystFinder.com

p.s. I tried to load the file using Excel, but it balked. Turns out with the first field being called ID, it thought it was a sylk file. Interestingly, if I changed that to id (using notepad), it stopped complaining. I wonder if that was the problem when you originally tried to upload the file from Excel.

[ahhh]

Thank you..  some id variable values actually had a hyphen in them. And those variable values are being displayed as missing. Sorry I left that while creating sample file. Please use the same code you have used before with this following sample file (attachment). Please let me know of any ideas to fix this..

Thanks!!

[art297]

I used the same proc import code I suggested in my last post and all field appear to import correctly.

Art, CEO, AnalystFinder.com