topic Re: proc import-ERROR in Statistical Procedures

proc import-ERROR

robertrao — Thu, 30 Aug 2012 13:47:53 GMT

I am sure about the code but there is an error. Please correct me

PROC IMPORT DATAFILE="C:\Users\Desktop\abc.xls" OUT=work.test

DBMS =excel REPLACE;

SHEET="SHEETNAME";

GETNAMES=YES;

RUN;

NOTE: The SAS System stopped processing this step because of errors.

1 PROC IMPORT DATAFILE="C:\Users\kdiris\Desktop\Benchmarks.xls" OUT=work.test

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

ERROR 76-322: Syntax error, statement will be ignored.

2 DBMS= Excel replace;

3 SHEET="Mortality";

4 GETNAMES=yes;

5 RUN;

Re: proc import-ERROR

Doc_Duke — Thu, 30 Aug 2012 13:59:41 GMT

Worked fine on my version of SAS 9.3. I suspect that you have some unprintable (control) character embedded in the code. Use a hex editor to look at it.

Re: proc import-ERROR

robertrao — Thu, 30 Aug 2012 14:03:38 GMT

Thanks a lot. It worked out for me too. Could you also help me put this code in a macro???

for example i want to import 5 sheets to sasuser with 5 different names in sas 9.2

Thanks

Re: proc import-ERROR

robertrao — Thu, 30 Aug 2012 14:05:03 GMT

5 sheets(tabs) from the same Excel spread sheet

Re: proc import-ERROR

robertrao — Thu, 30 Aug 2012 14:14:17 GMT

WILL THIS SERVE MY PURPOSE?/

%macro import(dsetname, sheetname);

PROC IMPORT DATAFILE="C:\Users\Desktop\abc.xls" OUT=sasuser.&dsetname

DBMS =excel REPLACE;

SHEET=&sheetname;

GETNAMES=YES;

RUN;

%mend;

Re: proc import-ERROR

Tom — Thu, 30 Aug 2012 15:02:14 GMT

The code looks fine. It is complaining about either the quotes around the filename or some hidden character before the quotes.

The quotes look normal in your post, but perhaps in your code they are actually a different character. For example Microsoft editors have a "feature" that automatically changes normal quotes into characters where the opening quotes lean to the left and the closing quotes lean to the right. Microsoft calls this "smartquotes", but I like to call them dumb quotes.

Re: proc import-ERROR

robertrao — Thu, 30 Aug 2012 15:20:03 GMT

Thanks Tom. Yesterday Arthur and me had a long discussion on the title " "Excel to SAS without using SQL"

i have my date in the name Quarter and values like 2010Q1 2010Q4 2012Q1 etc in Excel.(when i converted to dataset i am getting it as charecter and when i run the below code I am getting an error.

Could you please help????????

libname XL Excel "C:\Users\dump\Desktop\Bodeee.xlsx";

data sasuser.Length (drop=maxdate);

retain maxdate;

set sasuser.Length (in=inA) XL.'Los$'n;

if inA then maxdate=max(maxdate,QUARTER);

else if QUARTER gt maxdate;

run;

*ERROR;

Character values have been converted to numeric values at the places given by:

(Line):(Column).

15:33 16:9

NOTE: Invalid numeric data, QUARTER='2010 Q4' , at line 15 column 33.

Re: proc import-ERROR

Tom — Thu, 30 Aug 2012 16:32:34 GMT

The variable QUARTER is character. You tried to us it as a number in the MAX() function. So SAS attempted to convert QUARTER to a number for you. You also seem to have a space embedded in the value.

You should be able to convert it using INPUT() function.

data example;

infile cards dlm='|';

input quarter $ date date9.;

qtr = input(compress(quarter),yyq6.);

format qtr yyq6. date date9.;

put quarter = $quote. qtr= date= @;

if qtr > date then put 'Before';

else put 'NOT before';

cards;

2010q3|01jan2010

2010 q1|01jan2011

run;

quarter="2010q3" qtr=2010Q3 date=01JAN2010 Before

quarter="2010 q1" qtr=2010Q1 date=01JAN2011 NOT before

Re: proc import-ERROR

robertrao — Thu, 30 Aug 2012 16:52:22 GMT

Great help. You are not only helping with solutions but finding out errors in the sample data we provide....

Thanks for your time