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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
Doc_Duke
Rhodochrosite | Level 12

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.

robertrao
Quartz | Level 8

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

robertrao
Quartz | Level 8

5 sheets(tabs) from the same Excel spread sheet

robertrao
Quartz | Level 8

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;

Tom
Super User Tom
Super User

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.

robertrao
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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

robertrao
Quartz | Level 8

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

Thanks for your time

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 ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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