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

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

ahhh — Tue, 17 Oct 2017 03:43:19 GMT

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!

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

art297 — Tue, 17 Oct 2017 04:00:01 GMT

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

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

error_prone — Tue, 17 Oct 2017 04:04:27 GMT

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

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

Reeza — Tue, 17 Oct 2017 05:36:34 GMT

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.

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

error_prone — Tue, 17 Oct 2017 06:19:31 GMT

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

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

Kurt_Bremser — Tue, 17 Oct 2017 07:21:04 GMT

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

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

ahhh — Tue, 17 Oct 2017 14:49:24 GMT

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

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

Kurt_Bremser — Tue, 17 Oct 2017 15:04:59 GMT

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

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

ballardw — Tue, 17 Oct 2017 15:07:53 GMT

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

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

ahhh — Tue, 17 Oct 2017 15:12:49 GMT

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

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

Kurt_Bremser — Tue, 17 Oct 2017 15:49:55 GMT

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

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

ahhh — Tue, 17 Oct 2017 18:38:30 GMT

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

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

art297 — Tue, 17 Oct 2017 19:55:10 GMT

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.

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

ahhh — Wed, 18 Oct 2017 02:22:04 GMT

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

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

art297 — Wed, 18 Oct 2017 02:37:01 GMT

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

Art, CEO, AnalystFinder.com

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

ahhh — Wed, 18 Oct 2017 02:40:54 GMT

Even the rows 8,9,10 with ids like 100-09080??

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

art297 — Wed, 18 Oct 2017 04:15:16 GMT

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

Art, CEO, AnalystFinder.com

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

Kurt_Bremser — Wed, 18 Oct 2017 06:19:36 GMT

Always read ID variables as character:

data want;
infile cards dlm=',' dsd truncover;
input
  ID :$10.
  Date :mmddyy10.
  consolidated :$20.
  Ag_Ab_OR1
  Ag_Ab_S_CO1
  Ag_Ab_Od2
  Ag_Ab_a_O2
  Ag_Ab_OD3
  Ag_Ab_a_O3
  Antigen :$1.
  Antibody :$1.
  STI :$1.
  V_1_supplemental :$20.
;
label
  ID = 'ID'
  Date = 'Date'
  consolidated = 'consolidated'
  Ag_Ab_OR1 = 'Ag_Ab OR1'
  Ag_Ab_S_CO1 = 'Ag_Ab S/CO1'
  Ag_Ab_Od2 = 'Ag_Ab Od2'
  Ag_Ab_a_O2 = 'Ag_Ab a/O2'
  Ag_Ab_OD3 = 'Ag_Ab OD3'
  Ag_Ab_a_O3 = 'Ag_Ab a/O3'
  Antigen = 'Antigen'
  Antibody = 'Antibody'
  STI = 'STI'
  V_1_supplemental = 'V-1_supplemental'
;
format
  date mmddyy10.
;
cards;
98888888,6/12/2011,Reactive,0.089,0.099,0.678,,,,-,+,,
97777777,6/27/2010,non-reactive,0.987,,0.567,11.5,,,-,-,,negative
96543216,8/1/2007,Reactive,,0.987,,33.33,,5.78,+,-,,
97778881,10/3/2006,Reactive,0.099,,0.456,23.22,6.89,,-,+,,equal
92188881,4/4/2009,non-reactive,0.876,0.777,,,,,+,-,,positive
92222227,1/14/2010,d/c-TT+,0.678,0.543,0.345,,,,-,-,-,
100-09080,2/14/2011,,,,,,,,,,,
200-08070,,,0.789,,,,,,,,,
100-50678,3/24/2008,d/c-TT+,,8.9,,34.56,78.9,6.9,,,,negative
;
run;

proc print data=want label noobs;
run;

Clean log, and clean result:

                                        Ag_Ab   Ag_Ab   Ag_Ab   Ag_Ab   Ag_Ab   Ag_Ab
   ID             Date   consolidated    OR1    S/CO1    Od2     a/O2    OD3     a/O3   Antigen   Antibody   STI   V-1_supplemental

98888888    06/12/2011   Reactive       0.089   0.099   0.678     .       .       .        -         +                             
97777777    06/27/2010   non-reactive   0.987    .      0.567   11.50     .       .        -         -                 negative    
96543216    08/01/2007   Reactive        .      0.987    .      33.33     .      5.78      +         -                             
97778881    10/03/2006   Reactive       0.099    .      0.456   23.22    6.89     .        -         +                 equal       
92188881    04/04/2009   non-reactive   0.876   0.777    .        .       .       .        +         -                 positive    
92222227    01/14/2010   d/c-TT+        0.678   0.543   0.345     .       .       .        -         -        -                    
100-09080   02/14/2011                   .       .       .        .       .       .                                                
200-08070            .                  0.789    .       .        .       .       .                                                
100-50678   03/24/2008   d/c-TT+         .      8.900    .      34.56   78.90    6.90                                  negative