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.
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).
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;
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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.