Programming the statistical procedures from SAS

proc import-ERROR

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

proc import-ERROR

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


                          -


                          22


                          76


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;


Accepted Solutions
Solution
‎08-30-2012 12:32 PM
Super User
Super User
Posts: 6,699

Re: proc import-ERROR

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

View solution in original post


All Replies
Trusted Advisor
Posts: 2,114

Re: proc import-ERROR

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.

Super Contributor
Posts: 1,040

Re: proc import-ERROR

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

Super Contributor
Posts: 1,040

Re: proc import-ERROR

5 sheets(tabs) from the same Excel spread sheet

Super Contributor
Posts: 1,040

Re: proc import-ERROR

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;

Super User
Super User
Posts: 6,699

Re: proc import-ERROR

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.

Super Contributor
Posts: 1,040

Re: proc import-ERROR

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)Smiley SadColumn).


15:33   16:9


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

Solution
‎08-30-2012 12:32 PM
Super User
Super User
Posts: 6,699

Re: proc import-ERROR

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

Super Contributor
Posts: 1,040

Re: proc import-ERROR

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

Thanks for your time

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 991 views
  • 0 likes
  • 3 in conversation