coditional format excel import

[TimMandell]

Hello,

Below are cobbled together some really nice dynamic SAS code to import excel workbooks and their uniquie sheets from a given location.

I'm having trouble though assembling the imported data into one data set with fixed data types.

The data may have up 3000 obeservervations at the top of the data with no data in a given column.

SQl is used to create a case when statement to provide the apporpriate data type however I continue to get the same error as any other method of import and assembly;

ERROR: INPUT function requires a character argument.

ERROR: Numeric format F in PUT function requires a numeric argument.

The concept is to process the excel workbooks as is without manipulation.

Any ideas that do not include scripting save the sheets into CSV?

CODE:

"

** CREATE THE TABLE WITH COLUMNS BROAD ENCOUGH TO ACCEPT ALL YOUR DATA;

%Let PATHER = D:\TEST_EXCEL\;

PROC SQL NOCONSTDATETIME NOPRINT;

** FIND ALL THE EXCEL FILES IN A LOCATION;

data xls_files;

keep filename;

length fref $8 filename $80;

rc = filename(fref, "&PATHER");

if rc = 0 then do;

did = dopen(fref);

rc = filename(fref);

end;

else do;

length msg $200.;

msg = sysmsg();

put msg=;

did = .;

end;

if did <= 0 then putlog 'ERR' 'OR: Unable to open directory.';

dnum = dnum(did);

do i = 1 to dnum;

filename = dread(did, i);

fid = mopen(did, filename);

if fid > 0 and index(filename,".xl") then output;

end;

rc = dclose(did);

run;

*PULL ALL THE SHEETS INTO A DATA SET WITH REUSABLE SHEET# AS THE DATASET NAME;

%macro ReadXls (inf, dir, ord);

libname excellib excel "&dir.&inf"; /* STEP 1 */

proc sql ; /* STEP 2 */

create table sheetname as

select tranwrd(tranwrd(Memname,' ',''),'$','') as sheetname

from sashelp.vstabvw

where libname="EXCELLIB";

select count(DISTINCT sheetname) into :cnt_sht

from sheetname;

select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)

from sheetname;

quit;

libname excellib clear; /* STEP 3 */

%do i=1 %to &cnt_sht;

** IF THIS IS FIRST SHEET FIRST WORK BOOK MAKE A DATA SET;

%if &ord =1 and &cnt_sht =1 %then %do;

proc import datafile="&dir.&inf"

out= base replace;

sheet="&&sheet&i";

getnames=yes;

mixed=yes;

run;

proc sql ;

create table base as

select monotonic() as row, *,"&&sheet&i" as src from base;

run;

** EVALUATE THE DATASET FORMAT BY MOVING INTO VARABLES;

data sleep; call sleep(10,.01); run;

PROC SQL ; select distinct type INTO :tes1 from DICTIONARY.columns where memname ='BASE' AND name ='COL_1'; quit;

PROC SQL ; select distinct type INTO :tes2 from DICTIONARY.columns where memname ='BASE' AND name ='COL_2'; quit;

PROC SQL ; select distinct type INTO :tes3 from DICTIONARY.columns where memname ='BASE' AND name ='COL_3'; quit;

** EVALUATE VARIABLE AND FLATTEN TABLE INTO ONE FORMAT;

proc sql ;

create table master as(

select

row

,case when %TSLIT(&&tes1) like '%num%' then put(COL_1, 25.20) else input( COL_1, $25.) end as COL_1

,case when %TSLIT(&&tes2) like '%num%' then put(COL_2, 25.20) else input( COL_2, $25.) end as COL_2

,case when %TSLIT(&&tes3) like '%num%' then put(COL_3, 25.20) else input( COL_3, $25.) end as COL_3

,src

from base);

run;

%end;

**APPEND THE DATASET WITH FLATTENED FORMAT TO FIRST TABLE;

proc import datafile="&dir.&inf" /* STEP 4 */

out= sheet&i replace;

sheet="&&sheet&i";

getnames=yes;

mixed=yes;

run;

proc sql ;

create table base as

select monotonic() as row, *, "&&sheet&i" as src from sheet&i;

run;

data sleep; call sleep(10,.01); run;

PROC SQL ; select distinct type INTO :tes1 from DICTIONARY.columns where memname ='BASE' AND name ='COL_1'; quit;

PROC SQL ; select distinct type INTO :tes2 from DICTIONARY.columns where memname ='BASE' AND name ='COL_2'; quit;

PROC SQL ; select distinct type INTO :tes3 from DICTIONARY.columns where memname ='BASE' AND name ='COL_3'; quit;

proc sql ;

create table baser as(

select

row

,case when %TSLIT(&&tes1) like '%num%' then put(COL_1, 25.20) else input( COL_1, $25.) end as COL_1

,case when %TSLIT(&&tes2) like '%num%' then put(COL_2, 25.20) else input( COL_2, $25.) end as COL_2

,case when %TSLIT(&&tes3) like '%num%' then put(COL_3, 25.20) else input( COL_3, $25.) end as COL_3

,src

from base);

run;

proc append base=master data=base force; /* STEP 5 */

run;

　

%end;

%mend ReadXls;

data _null_;

set xls_files(obs=1);

file_name=filename;

call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||', 1))');

run;

data _null_;

set xls_files(firstobs=2);

file_name=filename;

call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||', 2))');

run;

"

[Reeza]

Create a master table that has the types you need in a file. 

Import the datasets as is. 

Run through EACH variable and compare it against the required type (use VTYPE) and do the conversions as nessary.

Append the results to your master table. 

CSV is easier IMO, but I've done this as well. 

if vtype(var1)='C' then var1_desired = input(var1, 8.);
else var1_desired=var1;

[Reeza · Accepted solution]

And there's the DBSASTYPE option as well that may work for you.

I partially assumed that's not an option for some reason.

[TimMandell]

using the following would be smarter on completely unknown data;

if vtype(var1)='C' then var1_desired = input(var1, 8.);
else var1_desired=var1;

Because I did know a few things up front I went with DBSASTYPE as shown below.

Please comment if you see an error I may have over looked.

options MACROGEN MPRINT mlogic;

%Let PATHER = D:\TEST;

** CREATE THE TABLE WITH COLUMNS BROAD ENCOUGH TO ACCEPT ALL YOUR DATA;

**set the master table;

Data work.master;

attrib

col_1 length =8 format = best25.18

col_2 length =8 format = best25.18

col_3 length =8 format = best25.18

;

Stop;

Run;

PROC SQL NOCONSTDATETIME PRINT;

data xls_files;

keep filename;

length fref $8 filename $80;

rc = filename(fref, "&PATHER");

if rc = 0 then do;

did = dopen(fref);

rc = filename(fref);

end;

else do;

length msg $200.;

msg = sysmsg();

put msg=;

did = .;

end;

if did <= 0 then putlog 'ERR' 'OR: Unable to open directory.';

dnum = dnum(did);

do i = 1 to dnum;

filename = dread(did, i);

fid = mopen(did, filename);

if fid > 0 and index(filename,".xl") then output;

end;

rc = dclose(did);

run;

%macro ReadXls (inf, dir);

libname excellib excel "&dir.&inf"; /* STEP 1 */

proc sql noprint; /* STEP 2 */

create table sheetname as

select tranwrd(tranwrd(Memname,' ',''),'$','') as sheetname

from sashelp.vstabvw

where libname="EXCELLIB";

select count(DISTINCT sheetname) into :cnt_sht

from sheetname;

select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)

from sheetname;

quit;

libname excellib clear; /* STEP 3 */

%do i=1 %to &cnt_sht;

libname myexcel odbc

required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};

dbq=&dir.&inf";

data base;

set myexcel."%sysfunc(TRIM(&sheet&i))$"n

(dbsastype=(col_1= 'NUMERIC' col_2= 'NUMERIC' col_3= 'NUMERIC'

)

);

RUN;

/* STEP 5 */

proc append base=master

data=base force;

run;

%end;

%mend ReadXls;

data _null_;

set xls_files(obs=1);

file_name=filename;

call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||'))');

run;

data _null_;

set xls_files(firstobs=2);

file_name=filename;

call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||'))');

run;