Programming the statistical procedures from SAS

Excel to SAS-updations each quarter!!

Reply
Super Contributor
Posts: 1,040

Excel to SAS-updations each quarter!!


Hi

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

Respected Advisor
Posts: 4,606

Re: Excel to SAS-updations each quarter!!

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;

PG

PG
Super Contributor
Posts: 1,040

Re: Excel to SAS-updations each quarter!!

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

Super Contributor
Posts: 1,040

Re: Excel to SAS-updations each quarter!!

I mean in a Macro or other SAS9.2 Procedures

Respected Advisor
Posts: 4,606

Re: Excel to SAS-updations each quarter!!

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 Smiley Very HappyateMax 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;

PG

PG
Super Contributor
Posts: 1,040

Re: Excel to SAS-updations each quarter!!

Thanks a lot

Ask a Question
Discussion stats
  • 5 replies
  • 128 views
  • 0 likes
  • 2 in conversation