Excel to SAS-without using SQL

[art297]

Actually, I don't think I've ever mentioned it here but, with computers, it can be a life saver and SHOULD be mentioned from time to time by someone!

[robertrao]

Hi Arthur,

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.

[art297]

We (the Forum) would have to know how that field is represented both in your Excel workbook and in your SAS dataset.

The example you showed looks like text and includes a space between year and quarter.  Is that the way it appears in both files?

[robertrao]

Hi Arthur,

I am sorry for the miscommunication.

in excel i enter it as 2010Q1 AND SO ON.......There is no space

since it is the same excel i import i will get the same value in my dataset(as a charecter variable)

But when it comes to the comparision step of same excel (updated) with my previous dataset(to be updated) it says char values have been converted to numeric.....

maybe bcos we are using "gt" while we are comparing!!!!!!

[robertrao]

The sas datasets(3 datasets from 3 from tabs ) are created first from the Excel sheet.

Later on every new quarter the same excel sheet is updated with new information and and only the new information needs to be updated in the 3 datasets which were created in the previous quarter....

quarter is charecter in the excel sheet(i enter it manually) and so it comes up as charecter in the 3 dataset too....

[art297]

I think the problem is that you are trying to compare a character string when, really, you want to be comparing a date.

If I understand the problem correctly, the following should represent both your data and the desired solution:

data myDatasetA;

  input Quarter $ x;

  cards;

2010Q1 1

2010Q2 1

2010Q3 1

2010Q4 1

;

run;

data XL_A;

  input Quarter $ x;

  cards;

2010Q1  2

2010Q2  2

2010Q3  2

2010Q4  2

2011Q1  2

2011Q2  2

2011Q3  2

2011Q4  2

;

run;

/*next, here is the code, but modified to work with 2 sas datasets, rather than expecting one to be from Excel */

data myDatasetA (drop=maxdate);

  retain maxdate;

  set myDatasetA (in=inA) XL_A;

  if inA then maxdate=max(maxdate,input(Quarter,yyq6.));

  else if input(Quarter,yyq6.) gt maxdate;

run;

[robertrao]

As usual,

Thank you once again.Not only you are helping us with answers but finding out the mistakes in our questions. Great job!!!

Learning a lot from you

Job is perfectly executed.

[Linlin]

please try replacing  "QUARTER" with  "input(QUARTER,yyq9.)" in your sas code.

[robertrao]

Thanks Lin

[robertrao]

Hi Arthur,

Yesterday we discussed some example with the date in yyq6 format.

here in my case i enter the date as 2010q1 and so on and so when i convert to a dataset its stil a charecter(with no formats attached to it);

So i beleive i am getting that error.

could you provide me with a n alternative solution to this problem

[art297]

Karun,

I posts apparently crossed at the same time.  You had two files, a SAS dataset and an Excel workbook.  Is QUARTER character in both, or just one and, if so, which one?  And, does it contain a space between YYYY and QX?