topic Re: Excel to SAS-without using SQL in Statistical Procedures

Excel to SAS-without using SQL

robertrao — Tue, 28 Aug 2012 21:24:44 GMT

PGstats helped me with a solution in Sql. Could anyone help me writing it in Macro or other basic steps??

I have an Excel spread sheet(X) and has 3 tabs in it(A B C) which are to be imported into 3 datasets in SAS.

The 3 datasets created will be our master datasets.

We need to update latest information from Excel to our master datasets

Our Excel sheet will have previous information too, BUT ONLY THE CURRENT QUARTER INFORMATION NEEDS TO BE APPENDED TO OUR MASTER DATASETS..

Could someone help me build the code in Sas 9.2.

Thanks in advance

Re: Excel to SAS-without using SQL

art297 — Tue, 28 Aug 2012 21:48:49 GMT

Replicating PG's logic in a datastep:

libname XL Excel "MyExcelFile.xlsx";

data want (drop=maxdate);

retain maxdate;

set myLib.myDatasetA (in=inA) XL.'A$'n;

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

else if myDate gt maxdate;

run;

Re: Excel to SAS-without using SQL

PGStats — Tue, 28 Aug 2012 22:10:08 GMT

Nice and compact! I think the logic calls for maxdate = max(myDate, maxdate) instead of maxdate=max(myDate, lag(myDate)) which would only give you the maximum of the two last dates in the dataset. - PG

Re: Excel to SAS-without using SQL

art297 — Tue, 28 Aug 2012 22:28:03 GMT

PG: Agreed and modified the code accordingly. It worked on my sample data as it was in date order.

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 13:01:28 GMT

Hi Arthur,

Firstly Thanks for your effort in putting up the code.

Since I am a novice, I have some difficulty understanding the code. Please correct me if i am wrong in understanding it!

1)we are reading our Excel file with the libname statement.

2)data want (drop=maxdate);

what is data want?why are we dropping maxdate??????and again retaining it????i dont want to create another dataset. just wanted to update

datasetA datasetB and datasetC from EXCEL (excel is updated every quarter)!!!

3)set myLib.myDatasetA (in=inA) XL.'A$'n;

To the DatasetA present in our library we are setting the information present in our Excel Tab1;(n specifies the tab)!!!

4) if inA maxdate = max(myDate, maxdate)

else if myDate gt maxdate;

What is this final step doing>??????????

THANKS A TON

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 13:16:18 GMT

Hi PGstats,

Thanks for making corrections to Arthurs code.

Since I am a novice, I have some difficulty understanding the code. Please correct me if i am wrong in understanding it!

1)we are reading our Excel file with the libname statement.

2)data want (drop=maxdate);

what is data want?why are we dropping maxdate??????and again retaining it????i dont want to create another dataset. just wanted to update

datasetA datasetB and datasetC from EXCEL (excel is updated every quarter)!!!

3)set myLib.myDatasetA (in=inA) XL.'A$'n;

To the DatasetA present in our library we are setting the information present in our Excel Tab1;(n specifies the tab)!!!

4) if inA maxdate = max(myDate, maxdate)

else if myDate gt maxdate;

What is this final step doing>??????????

THANKS A TON

Re: Excel to SAS-without using SQL

art297 — Wed, 29 Aug 2012 13:25:51 GMT

The code was intended to mimic what PG's SQL code had done:

1. Yes, we are reading the Excel file with the libname statement

2. The datastep is creating a file called "want". You can give it whatever name you want. For examples, I usually call the input data 'have' and the output file 'want'. Maxdate was dropped as I didn't think you needed it beyond using it to determine which Excel rows you wanted to add. The code was only written to update one dataset based on a tab within an Excel workbook. Of course, it could easily be expanded to deal with multiple tabs and/or sas dataset updates.

Maxdate is retained so that its value holds, from one record to the next, unless the program changes it. That way, the value continues to be available while you are reading the data from the workbook.

3. In PG's code he assumed a tab labeled 'A' which, written as a name literal (in his code) was expressed as 'A$'n

4. The program is first reading and rewriting all of the records that are in the current sas dataset and, by the time it gets to the last record, having determined what the latest date was that was in the file. Then, when it reads the tab in the workbook, it ONLY selects those rows that have a value for myDate that is more recent than maxdate.

Does that answer all of your questions?

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 14:03:30 GMT

Dear Arthur,

Some more questions

1)Apart from the latest data being updated in myLib.myDatasetA the same data is seen in the WANT dataset we are creating here?

2)if mydate is the variable containing the dates in the dataset? then which part of ouir code is comparing our dates in the dataset with the dates present in Excel????

3)if my date is in the format 2011Q3 and so on this code will still hold good right?

4)if inA then maxdate=max(maxdate,myDate); /*holding the max date in our dataset*/

else if myDate gt maxdate; /*what is this step doing? "else if myDate gt maxdate" then what should it do? is there anything missing here*/

Re: Excel to SAS-without using SQL

art297 — Wed, 29 Aug 2012 14:32:47 GMT

Karun,

It is probably easiest to answer your questions with an example that only uses sas datasets so that you can see what the data looks like in a data step.

First, here are two datasteps that create files which I think are like the ones you have, other than that the second one is really a tab within an Excel workbook:

data myDatasetA;

informat myDate yyq6.;

format myDate yyq6.;

input myDate x;

cards;

2010Q1 1

2010Q2 1

2010Q3 1

2010Q4 1

;

run;

data XL_A;

informat myDate yyq6.;

format myDate yyq6.;

input myDate x;

cards;

2010Q1 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 want (drop=maxdate);

retain maxdate;

set myDatasetA (in=inA) XL_A;

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

else if myDate gt maxdate;

run;

/* the resulting file will look like:

myDate x

2010Q1 1

2010Q2 1

2010Q3 1

2010Q4 1

2011Q1 2

2011Q2 2

2011Q3 2

2011Q4 2

Thus, to answer your questions:

1. The data in myDatasetA doesn't get changed .. only new records are added to it

2. Before file XL_A is even read, maxdate was set to equal the maximum date present in the file myDatasetA. It was retained so that it would be available at that time when the records from XL_A were read

3. The format of myDate shouldn't matter as long as it was input correctly to represent a SAS date

4. Yes, you appear to understand the first part. The second part is a SAS statement that selects records from XL_A if, and only if, their value for myDate is greater than maxdate. An easy way to see that work is with another example. e.g.:

data test;

set sashelp.class;

if sex eq "M";

run;

/* That code will ONLY select records that have a value of "M" for the variable called sex */

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 14:43:33 GMT

WOW ARTHUR,

Thanks for your patience and Time. PGstats and you deserve a Lunch. I am going to save this example and explanation for the future so that I will not take your valuable time for the same task again.

QUESTION: I am confused also on to whoi among u both should be given a RIGHT ANSWER!!! There should have been a BOTH OPTION FOR THAT........

WeLL Done

Re: Excel to SAS-without using SQL

art297 — Wed, 29 Aug 2012 14:51:58 GMT

Karun: Yes, it would be nice to be able to assign multiple correct answers, but it is more important that you assign at least one of them as being correct so that others won't use up their time trying to answer a question they don't think has been answered yet.

You can assign one correct answer and, I think, up to 3 other posts as being helpful. Assigning those credits serves two purposes: (1) it makes it easier for people to find the correct answer when they have a similar question and (2) it is the only reward we users get for helping other users. Not much of an award as it can't be used for anything, but at least it does seem to motivate people to answer posts. FWIW, a correct answer gets 4 points and each helpful answer gets 3 points.

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 18:40:47 GMT

Hi Arthur,

The code u built keeps updating the values from Excel to our dataset BUT WITH A DIFFERENT NAME(WANT).

WANT has the information now.

Is there a way to do the same process without creating WANT dataset?????????

i want data from Excel to be added to myDatasetA which is already present in a particular library?????

Regards

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 18:47:56 GMT

*WILL THIS WORK?

changed want to data myLib.myDatasetA??????????????

libname XL Excel "MyExcelFile.xlsx";

data myLib.myDatasetA (drop=maxdate);

retain maxdate;

set myLib.myDatasetA (in=inA) XL.'A$'n;

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

else if myDate gt maxdate;

run;

Re: Excel to SAS-without using SQL

art297 — Wed, 29 Aug 2012 19:07:44 GMT

Karun, Yes! As far as I can tell, that is precisely how it should look.

However, just to play it safe, you may want to make a copy of mylib.myDatasetA, before running the code. The code will physically modify mylib.myDatasetA and, if it isn't exactly how you want it, without having a backup copy you will be totally out of luck.

Have I ever mentioned the importance of maintaining backup copies of one's datasets?

Re: Excel to SAS-without using SQL

robertrao — Wed, 29 Aug 2012 19:11:17 GMT

Thanks. I recently joined this community. I dint have a chance to hear about the backup maintainance

Re: Excel to SAS-without using SQL

art297 — Wed, 29 Aug 2012 19:40:39 GMT

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!

Re: Excel to SAS-without using SQL

robertrao — Thu, 30 Aug 2012 15:21:58 GMT

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.

Re: Excel to SAS-without using SQL

robertrao — Thu, 30 Aug 2012 15:39:10 GMT

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

Re: Excel to SAS-without using SQL

art297 — Thu, 30 Aug 2012 15:39:31 GMT

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?

Re: Excel to SAS-without using SQL

Linlin — Thu, 30 Aug 2012 15:46:15 GMT

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