Blank Spreadsheet Columns Counted As Numeric Instead of Character

[AIara]

Hi! I am using  SAS 9.4. I have a group of about 50 spreadsheets that I need to import and append. The data on the spreadsheets are all in cells referencing other cells. This means that if the referenced cell is blank, Excel automatically puts a 0. 

I have the following macro that will import all of the spreadsheets into SAS. I use the DBTYPE command to tell SAS if the variable is numeric or character. This usually works great. However, if the imported column is completely blank (technically all 0's), SAS is putting the variable as numeric even if I told it to make it character. 

For example, the spreadsheet has a column called "Description" where staff write the description of the issue. Some of them are filled in and some of them are blank.

Description

Bruised Knee
Broken Arm
0
Diabetes
0
0

Sometimes there are no descriptions:

Description 

0

0

0

0

0

SAS imports the first example as a character variable, and the second example as a numeric variable. When I go to append the sets, I get an error because it cannot combine numeric and character variables.

Does anyone know a way where I can make the variable character regardless of the contents? I thought stating that it is a character in DBTYPE would be enough. Thanks!

Here is the code I am working with:

/*pull files macro*/

%macro drive(dir,ext); 
%local cnt filrf rc did memcnt name; 
%let cnt=0;

%let filrf=mydir; 
%let rc=%sysfunc(filename(filrf,&dir)); 
%let did=%sysfunc(dopen(&filrf));
%if &did ne 0 %then %do; 
%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt; 

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.); 

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
%if %superq(ext) = %superq(name) %then %do; 
%let cnt=%eval(&cnt+1); 
%put %qsysfunc(dread(&did,&i)); 
proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=ci&cnt 
dbms=EXCEL;
dbdsopts= "dbtype=(date='numeric(10)' desc='char(500)' uas='char(20)' postuas='numeric(10)' 
prevuas='numeric(10)' prescore='numeric(10)' resp='char(200)' int='char(500)' appr='char(20)' reason='char(500)' comm='char(500)')";
sheet="CI Raw";
mixed = yes; 
run; 
%end; 
%end;

%end;
%end;
%else %put &dir cannot be open.;
%let rc=%sysfunc(dclose(&did)); 

%mend drive;

/*run macro*/

%drive( FOLDER PATH,xlsx)

/*append sets*/

data work.cisum;
set work.ci1 - work.ci50;
where date > 0;
run;

[PaigeMiller]

---

@AIara wrote:

Hi! I am using  SAS 9.4. I have a group of about 50 spreadsheets that I need to import and append. The data on the spreadsheets are all in cells referencing other cells. This means that if the referenced cell is blank, Excel automatically puts a 0. 

I have the following macro that will import all of the spreadsheets into SAS. I use the DBTYPE command to tell SAS if the variable is numeric or character. This usually works great. However, if the imported column is completely blank (technically all 0's), SAS is putting the variable as numeric even if I told it to make it character. 

---

This is not my experience using PROC IMPORT. If a cell is missing/blank in Excel, then it shows up as a missing in SAS.

Is it possible that in SAS you have set 

options missing='0';

somewhere (perhaps in your AUTOEXEC.SAS) and that's the cause?

What happens if as the first line of the program you set it to

options missing=.;

[AIara]

Hi! Thank you for responding. I tried setting the options and it did not work. I think the issue is that the cells are "0" in Excel, so SAS isn't considering them to be missing. 

I have code later on in my report to filter out the 0s, The issue is that character variables that are all 0s are being set as numeric variables. 

A colleague suggested that I use the PUT function to manually change the variables. 

[PaigeMiller]

Most of us here in the SAS communities will not download Excel files as they can be a security threat, so we can't really use your actual Excel file. Can you actually show us a screen capture of part of the Excel file where the missings cells appear as zero?

[AIara]

Thanks! I have attached a screenshot. 

My colleagues sent me these 50 workbooks. There are sheets that are forms/worksheets that employees fill out. They are heavily formatted for the user, so importing them into SAS would not be easy. To get around this, they made a second spreadsheet that holds the data for each form in a single line. 

The screenshot shows one of these back end spreadsheets. Each cell is referencing a question on the form/worksheet. If the employee does not fill out a field, the data spreadsheet is showing a 0. 

This is fine when at least one cell in the column has characters in it. For example, the variable "rati" has "No" in the first column. SAS is setting this as a character variable. However, the variable "oldn" is also a character variable, but SAS is setting it as numeric because it has all 0s in it. 

In another workbook, someone may have had the need to fill out "oldn". When I import all 50 spreadsheets, I now have one where "oldn" is listed as numeric and another one is listed as character.

[PaigeMiller]

You need to either:

1. clean up the process that creates these Excel files so that it doesn't cause your SAS program any problems
2. clean up the data in SAS after you import it from Excel

[AIara · Accepted solution]

Thanks! I agree. A coworker was able to help me figure this out. 

To clean up the data in Excel, we can change the reference show blank if the referenced cell is empty:

=if(reference cell="","",referenced_cell)

We decided to switch the variables within the SAS Macro To switch the variables over in SAS, we added the following into the macro for each affected variable:

data   ci&cnt(drop=oldvariablename);

  set ci&cnt;

  length newvariablename $500.;

  newvariablename=oldvariablename;

  run;    

Thank you for all of your help PaigeMiller!