DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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

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!


Accepted Solutions
Solution
‎10-23-2017 10:34 PM
PROC Star
Posts: 7,492

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

Looks correct to me. I've attached copies of the output.

 

Art, CEO, AnalystFinder.com

View solution in original post

Attachment

All Replies
PROC Star
Posts: 7,492

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

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

 

Frequent Contributor
Posts: 149

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

Maybe using guessingrow=max helps, but this will increase time required to import the file.
Super User
Posts: 19,878

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

Posted in reply to error_prone
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.
Frequent Contributor
Posts: 149

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

"Guessingrows isn’t a valid option for excel files."
Right, but it should be available Smiley Wink
Super User
Posts: 7,868

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 40

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

[ Edited ]

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 Smiley Sad

 

Thank you all in advance! 

 

Btw DBSASTYPE  did not work..

Super User
Posts: 7,868

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

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


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 Smiley Sad

 

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.

 

 

Contributor
Posts: 40

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

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

Super User
Posts: 7,868

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 40

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

Posted in reply to KurtBremser

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

PROC Star
Posts: 7,492

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

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.

 

 

Contributor
Posts: 40

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

[ Edited ]

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!!

PROC Star
Posts: 7,492

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

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

 

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 182 views
  • 4 likes
  • 6 in conversation