Proc Import with variable type specifications%0D%0A

[Lani1]

I'm out of practice with coding and attempting to import data from an Excel file, specifying that all variables should be CHAR (as I'll be appending the new dataset to an existing SAS dataset with CHAR variables).  Not sure why the following Proc Import code results in an error:  DBDSOPTS statement not valid or used out of proper order.  Is there a better way to do this?  (SAS Enterprise Guide 8.3).

proc import 
	datafile="\path\filename.xlsx"
	out= NewDataset
	dbms = xlsx
	replace;
	getnames=yes; 
 	DBDSOPTS="DBTYPE=(ID_nbr='CHAR(6)' City ='CHAR(50)')";	
run;

proc append
  base=ExistingDataset data=NewDataset;
  run;  

The variables in the two dataset will need to match in terms of data type and length before appending.  Coding using the Length statement did not work out, either.  

[Kathryn_SAS]

To use the DBDSOPTS, you have to specify DBMS=EXCEL. 

proc import  
out=class2
datafile="c:\temp\class2.xlsx"
dbms=excel replace;
sheet=class2;
dbdsopts="dbsastype=(Age='char(8)' Height='char(8)')";
run;

Another option is to add a row at the bottom of each spreadsheet containing a dummy character in each numeric column and import it with DBMS=XLSX engine (MIXED=YES  is the default).

[Tom]

If the column headers are text then just tell PROC IMPORT to NOT guess the names.  You can then rename the variables. 

PROC APPEND will only generate a WARNING if the lengths are shorter.  And you can use the FORCE option to have it truncate the values when the lengths are longer.

Make sure to skip the header line.

proc import 
  dbms = xlsx
  datafile="\path\filename.xlsx"
  out= NewDataset
  replace
;
  getnames=no; 
run;

proc append force
  base=ExistingDataset
  data=NewDataset(firstobs=2 rename=(
      a=ID_nbr b=City
   ))
;
run;

If you want to suppress the warning messages then run another step to change the lengths. You could even make that as a VIEW so you don't make an extra copy of the data.

proc sql;
create view for_append as
select trim(A) as ID_nbr length=6
     , time(B) as City length=50
from NewDataset(firstobs=2)
;
quit;
proc append force
  base=ExistingDataset
  data=for_append
;
run;

[Kurt_Bremser]

The usual advice also applies here: save the spreadsheet to a text (csv) file and read that with a DATA step, which gives you full control over how the data is read.