PROC IMPORT SCANTIME ERROR

[leo_oaks]

Hi everybody,

I'm new on SAS and I'm trying to import data from an excel sheet and I'm getting an error as seen below:

NOTE: Line generated by the invoked macro "CAPTURE".

2      SCANTIME=YES; RUN;

       --------

       180

ERROR 180-322: Statement is not valid or it is used out of proper order.

And this is my code:

%macro capture(sheet,cd,lang);

PROC IMPORT OUT= WORK.imp

            DATAFILE= "C:\SAS\Book1.xlsx"

            DBMS=XLSX REPLACE;

     RANGE="&sheet";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

data imp;

set imp;

REC_TYPE=SUBSTR(CELLID,1,2);

CELL=SUBSTR(CELLID,4,2);

fscl_yr=2013;

un_tp_cd=926;

uu_tp='BBS_PUB  ';

own_mad_no=&cd.;

bct_lang_cd=&lang.;

run;

data aar2013;

set aar2013 imp;

if OWN_MAD_NO=. THEN DELETE;

run;

%mend;

%capture('Sheet1$',236587,0);

%capture('Sheet2$',452658,2);

%capture('Sheet3$',784587,1);

%capture('Sheet4$',254145,0);

%capture('Sheet5$',985687,0);

So, I hope someone could give a hint about what is going on here.

Thank you!

Leo

[jakarman]

Wich OS SAS version.
At proc import scantime is not documented. At Access/PCfiles it is  SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition
There are differences using Windows/Unix see remarks on Ace driver. 

[leo_oaks]

Hi Jaap,

mine is 9.4 on Windows

[jakarman]

Windows 9.4, strange should work according docs or that one is only working with xls or excel excelcs types.

Time as type must be recognized in Excel. Never mind the way RW9 indicated is a more reliable approach.

[RW9 · Accepted solution]

I would suggest to save to CSV and then use datastep infile rather than proc import as this will allow you far more control and reliability than using Excel/Excel engines.  If you have multiple sheets then a simple VBA macro will save each sheet to a csv, something like: http://www.ehow.com/how_8477277_convert-excel-sheets-csv-macros.html

[leo_oaks]

I will try your approach RW9, thank you!