BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8


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

5 REPLIES 5
PGStats
Opal | Level 21

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
robertrao
Quartz | Level 8

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

robertrao
Quartz | Level 8

I mean in a Macro or other SAS9.2 Procedures

PGStats
Opal | Level 21

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;

PG

PG
robertrao
Quartz | Level 8

Thanks a lot

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
  • 5 replies
  • 1491 views
  • 0 likes
  • 2 in conversation