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;
... View more