BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ahhh
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

View solution in original post

17 REPLIES 17
art297
Opal | Level 21

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
Barite | Level 11
Maybe using guessingrow=max helps, but this will increase time required to import the file.
Reeza
Super User
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
Barite | Level 11
"Guessingrows isn’t a valid option for excel files."
Right, but it should be available 😉
ahhh
Obsidian | Level 7

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

ballardw
Super User

@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
Obsidian | Level 7

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

ahhh
Obsidian | Level 7

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

art297
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

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
  • 17 replies
  • 2873 views
  • 4 likes
  • 6 in conversation