BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leo_oaks
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
jakarman
Barite | Level 11

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 --<-----
leo_oaks
Calcite | Level 5

Hi Jaap,

mine is 9.4 on Windows

jakarman
Barite | Level 11

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 --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

I will try your approach RW9, thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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