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
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
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.
Hi Jaap,
mine is 9.4 on Windows
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.
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
I will try your approach RW9, thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.