Help using Base SAS procedures

PROC IMPORT SCANTIME ERROR

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

PROC IMPORT SCANTIME ERROR

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


Accepted Solutions
Solution
‎08-12-2014 10:29 AM
Super User
Super User
Posts: 7,970

Re: PROC IMPORT SCANTIME ERROR

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

View solution in original post


All Replies
Trusted Advisor
Posts: 3,214

Re: PROC IMPORT SCANTIME ERROR

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. 

---->-- ja karman --<-----
New Contributor
Posts: 3

Re: PROC IMPORT SCANTIME ERROR

Hi Jaap,

mine is 9.4 on Windows

Trusted Advisor
Posts: 3,214

Re: PROC IMPORT SCANTIME ERROR

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.

---->-- ja karman --<-----
Solution
‎08-12-2014 10:29 AM
Super User
Super User
Posts: 7,970

Re: PROC IMPORT SCANTIME ERROR

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

New Contributor
Posts: 3

Re: PROC IMPORT SCANTIME ERROR

I will try your approach RW9, thank you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 734 views
  • 0 likes
  • 3 in conversation