topic Re: PROC IMPORT SCANTIME ERROR in SAS Procedures

PROC IMPORT SCANTIME ERROR

leo_oaks — Tue, 12 Aug 2014 13:19:47 GMT

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

Re: PROC IMPORT SCANTIME ERROR

jakarman — Tue, 12 Aug 2014 13:56:29 GMT

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.

Re: PROC IMPORT SCANTIME ERROR

RW9 — Tue, 12 Aug 2014 14:29:00 GMT

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

Re: PROC IMPORT SCANTIME ERROR

leo_oaks — Tue, 12 Aug 2014 15:31:35 GMT

Hi Jaap,

mine is 9.4 on Windows

Re: PROC IMPORT SCANTIME ERROR

leo_oaks — Tue, 12 Aug 2014 15:32:52 GMT

I will try your approach RW9, thank you!

Re: PROC IMPORT SCANTIME ERROR

jakarman — Tue, 12 Aug 2014 15:44:55 GMT

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.