topic Re: Excel to SAS-updations each quarter!! in Statistical Procedures

Excel to SAS-updations each quarter!!

robertrao — Tue, 28 Aug 2012 14:53:54 GMT

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

Re: Excel to SAS-updations each quarter!!

PGStats — Tue, 28 Aug 2012 15:29:18 GMT

I would try something like this :

libname XL Excel "MyExcelFile.xlsx";

proc sql;
select max(myDate) into :myDateMax from myLib.myDatasetA;
insert into myLib.MyDatasetA (myDate, myDataX, myDataY)
select myDate, myDataX, myDataY
from XL.'A$'n
where myDate > &myDateMax;

/*... same for myDatasetB and myDatasetC */

quit;

libname XL clear;

Re: Excel to SAS-updations each quarter!!

robertrao — Tue, 28 Aug 2012 15:37:55 GMT

Dear PGstats,

thank you so very much for the quick response. It is always a joy to get replies. This website is great.

Is there also a way to do it without using Proc Sql? Could you also navigate me through?????

Thanks in advance

Re: Excel to SAS-updations each quarter!!

robertrao — Tue, 28 Aug 2012 15:41:11 GMT

I mean in a Macro or other SAS9.2 Procedures

Re: Excel to SAS-updations each quarter!!

PGStats — Tue, 28 Aug 2012 16:28:58 GMT

As you see every day on this forum, there are always many ways to get results with SAS. I will let others suggest alternatives to SQL. Here is a commented version of my code

/* Replace the names starting with "my" with your own names */

/* Associate the name XL with your Excel file. Include the full path

within quotes */

libname XL Excel "MyExcelFile.xlsx";

proc sql;

/* Store the last date already in your dataset myLib.myDatasetA

into macro variable DateMax */

select max(myDate) into :DateMax from myLib.myDatasetA;

/* Import the lines from tab A in your Excel file having a date

greater than DateMax and insert them in dataset

myLib.myDatasetA */

insert into myLib.MyDatasetA (myDate, myVarX, myVarY)

select myDate, myVarX, myVarY

from XL.'A$'n

where myDate > &DateMax;

Re: Excel to SAS-updations each quarter!!

robertrao — Tue, 28 Aug 2012 16:49:01 GMT

Thanks a lot