Solved: Re: Automatically calculate yearly average

naeemomer · Posted 01-16-2022 06:11 AM

Dear All,

I have searched the forum for the answer, but could not find something relevant to my query.

I have daily average data for two variables – the dataset keeps updating on daily basis (connected to live data source). For the calculation of a parameter, I need to get the average of both the variables, get their ratio and calculate the percentage increase or decrease over the period of year (also given as formula as follows).

I know that I need to setup Macro but how should it be? My questions are:

How to convert daily average to yearly average for the variables in SAS Code? I want to have the macro, which automatically separates each year and calculate the average for the variables (as data flows)?

var1_avg_year123 = 123.456

var2_avg_year123 = 234.567

Indicator_year123 = var1_avg_year123 / var2_avg_year123

2. The calculated average is then used in given formula to calculate the percentage change in the Performance Indicator.

Perf_Indicator(%) = ( Indicator_current_year – Indicator_last_year) / (Indicator_current_year) * 100

My question is, any clues about the solution, to have this kind of Macro?

Thanks,

Sample of data:

Date	Var1	Var2
1/4/2018	1052.361	1316.885
1/5/2018	1213.219	1733.21
1/6/2018	978.2041	1422.348
1/7/2018	680.1459	945.5225
1/8/2018	456.6003	617.2773
1/9/2018	150.2401	194.8029
1/10/2018	677.037	860.5974
1/11/2018	1469.71	1948.627
1/12/2018	1179.192	1552.934
1/13/2018	1298.249	1669.028
1/14/2018	1358.477	1603.955
1/15/2018	1221.706	1335.553
1/16/2018	1454.211	1362.172
1/17/2018	1721.99	1774.3
1/18/2018	1689.863	2157.368
1/19/2018	1040.45	1537.941
1/20/2018	859.5139	1232.655
1/21/2018	1426.132	2046.319
1/22/2018	938.5096	1365.816
1/23/2018	1457.251	2025.362
1/24/2018	881.5562	1198.344
1/25/2018	1480.552	1961.99
1/26/2018	1926.771	2479.684
1/27/2018	1916.55	2461.287
1/28/2018	1673.023	2225.212
1/29/2018	1765.874	2462.233
1/30/2018	1789.701	2533.19
1/31/2018	1676.445	2443.382
2/1/2018	1800.134	2418.202
2/2/2018	1921.747	2369.32
2/3/2018	2066.464	2468.181
2/4/2018	1959.351	2477.953
2/5/2018	1807.281	2434.835
2/6/2018	1601.57	2421.839
2/7/2018	1510.655	2424.343
2/8/2018	1605.063	2420.206
2/9/2018	1771.467	2414.867
2/10/2018	1929.182	2409.557
2/11/2018	1985.056	2441.965
2/12/2018	1907.433	2476.812
2/13/2018	1795.833	2494.92
2/14/2018	1858.474	2568.475
2/15/2018	1732.217	2672.946
2/16/2018	1729.425	2631.901
2/17/2018	1787.801	2611.558
2/18/2018	2054.658	2603.725
2/19/2018	2076.014	2589.475
2/20/2018	1895.95	2561.175
2/21/2018	1718.748	2509.298
2/22/2018	1751.495	2441.274
2/23/2018	1842.372	2366.673
2/24/2018	1805.444	2132.074
2/25/2018	1505.642	1904.716
2/26/2018	1396.847	1469.527
2/27/2018	1421.316	1704.773
2/28/2018	1714.233	2032.702
3/1/2018	1832.073	2150.518
3/2/2018	1932.319	2167.833
3/3/2018	1715.786	1861.284
3/4/2018	1458.251	1596.16
3/5/2018	1339.739	1611.28
3/6/2018	1410.955	1642.457
3/7/2018	1413.319	1791.306
3/8/2018	1509.37	1801.486
3/9/2018	1459.795	1757.712
3/10/2018	1472.163	1725.644
3/11/2018	1659.859	1656.797
3/12/2018	2004.291	1639.147
3/13/2018	1956.614	1617.089
3/14/2018	1694.647	1580.816
3/15/2018	1606.138	1611.78
3/16/2018	1603.294	1603.493
3/17/2018	1514.116	1576.456
3/18/2018	1368.427	1526.759
3/19/2018	1196.518	1511.448
3/20/2018	1215.701	1513.986
3/21/2018	1106.108	1360.411
3/22/2018	1363.992	1575.326
3/23/2018	1337.306	1697.756
3/24/2018	1260.276	1617.235
3/25/2018	1381.319	1691.405
3/26/2018	1483.169	1704.757
3/27/2018	1314.011	1637.299
3/28/2018	1477.24	1872.673
3/29/2018	1597.64	1957.048
3/30/2018	1674.141	1972.857
3/31/2018	1383.66	1674.452
4/1/2018	1379.385	1770.248
4/2/2018	1569.761	2275.148
4/3/2018	1474.417	2171.795
4/4/2018	1376.228	2210.642
4/5/2018	949.9828	1510.065
4/6/2018	1246.785	1886.985
4/7/2018	1138.834	1793.173
4/8/2018	1383.181	2248.09
4/9/2018	1365.797	2199.225
4/10/2018	1546.395	2403.06
4/11/2018	1383.692	2048.077
4/12/2018	1680.016	2285.013
4/13/2018	1775.622	2572.279
4/14/2018	1577.264	2345.104
4/15/2018	1423.98	2041.661
4/16/2018	1780.363	2149.781
4/17/2018	1466.3	1697.889
4/18/2018	80.1146	111.1365

PaigeMiller · Posted 01-16-2022 04:35 PM

Your data only has one year. Assuming this is just a part of your data, here is some UNTESTED CODE

proc summary nway data=have;
    class date;
    format date year.;
    var var1 var2;
    output out=means mean=;
run;
data want;
    set means;
    indicator_year=var1/var2;
    perf_indicator1=100*(indicator_year-lag(indicator_year))/indicator_year;
run;

The idea that you put forth that you need a macro to do this indicates you haven't learned basic DATA step manipulation and basic PROC SUMMARY/PROC MEANS. Learn these things. Learn these things before you even think about macros. SAS provides a huge amount of tools for computing basic statistics, such as a mean, and things like subtraction and division can be done in a DATA step. Learn these things before you try to write a macro. Do not use a macro here, it is simply unnecessary.

If you want tested code, provide data as DATA step code (instructions), and make sure that data has at least 2 years. Most of us will not work with "spreadsheet" data that you have provided.

--
Paige Miller

View solution in original post

JackHamilton · Posted 01-16-2022 04:17 PM

Before writing a macro, you should figure out how to do it in open code. That doesn't look like a problem where a macro will get you much more than parameterization.

If the solution doesn't have to be blazingly fast, you can do it in SQL. Search for "moving average in sql" in Google. Some of the solutions you'll find use features that aren't available in SAS's dialect of SQL.

Also, make sure you know what you mean by "year". 365 days ago? 365 days ago except in leap years when it's 366 days ago? Same day of the same month in the previous year? Same day of the same month in the previous year, but force it to a weekday? Or to a working day?

PaigeMiller · Posted 01-16-2022 04:35 PM

Your data only has one year. Assuming this is just a part of your data, here is some UNTESTED CODE

proc summary nway data=have;
    class date;
    format date year.;
    var var1 var2;
    output out=means mean=;
run;
data want;
    set means;
    indicator_year=var1/var2;
    perf_indicator1=100*(indicator_year-lag(indicator_year))/indicator_year;
run;

The idea that you put forth that you need a macro to do this indicates you haven't learned basic DATA step manipulation and basic PROC SUMMARY/PROC MEANS. Learn these things. Learn these things before you even think about macros. SAS provides a huge amount of tools for computing basic statistics, such as a mean, and things like subtraction and division can be done in a DATA step. Learn these things before you try to write a macro. Do not use a macro here, it is simply unnecessary.

If you want tested code, provide data as DATA step code (instructions), and make sure that data has at least 2 years. Most of us will not work with "spreadsheet" data that you have provided.

--
Paige Miller

naeemomer · Posted 01-24-2022 02:04 AM

Morning,

There seems to be an issue, either with my data-set (and Date format) or with the code you shared.

My data looks like the following (here is just a snapshot - the dataset contains 4 years of data points)

Using the code provided, I get following, which is definitely not what I am looking for. I am looking for yearly averages, for the two variables (which one can calculate in python using "df.resample('Y').sum()" ).
I used the following code and got the result illustrated (in attached figure). I shall appreciate any clues/hints to improve the solution in SAS Code
proc summary nway data=Sample_table;
	class Date;
	format Date year.;
	var AB2_Water_MBD AB2_PWR_MW;
	output out=means mean=;
title 'Yearly Average of the Water and Power';
run;

proc print data=means;
run;

Thanks and regards,

Omar

Reeza · Posted 01-24-2022 11:53 AM

That is because you have a datetime (05Mar2019:15:00:00), not a date (05Mar2019) variable so you have the wrong format applied.

(Your first post shows a date format not a datetime).

Try using DTYEAR. instead.

proc summary nway data=Sample_table;
	class Date;
	format Date dtyear.;
	var AB2_Water_MBD AB2_PWR_MW;
	output out=means mean=;
title 'Yearly Average of the Water and Power';
run;

proc print data=means;
run;

Not 100% sure if there is a DTYEAR format.

@naeemomer wrote:

Morning,

There seems to be an issue, either with my data-set (and Date format) or with the code you shared.

My data looks like the following (here is just a snapshot - the dataset contains 4 years of data points)
Using the code provided, I get following, which is definitely not what I am looking for. I am looking for yearly averages, for the two variables (which one can calculate in python using "df.resample('Y').sum()" ).

I used the following code and got the result illustrated (in attached figure). I shall appreciate any clues/hints to improve the solution in SAS Code
proc summary nway data=Sample_table;
	class Date;
	format Date year.;
	var AB2_Water_MBD AB2_PWR_MW;
	output out=means mean=;
title 'Yearly Average of the Water and Power';
run;

proc print data=means;
run;
Thanks and regards,

Omar

Reeza · Posted 01-16-2022 04:56 PM

If the data is updated daily, is it a YTD comparison or are you comparing a partial year to a full year? We have reports that include all three for example, a comparison of current quarter to same quarter previous year, YTD compared to YTD last year (ie Up to Jan 16 2021 compared to Up to Jan 16 2022) or YTD compared to full prior year (limited value until later in the year).

Just a note that if you do this and your business has cycles or seasonality it may make sense to align your start dates somehow which is also something we do as we have biweekly payment cycles. If we start one year with one week being a payment week and in the prior week it wasn't it makes it seem like we're always off.

Sorry if you know all of this but it wasn't clear from the post :).

SAS PROC MEANS can easily do all of these calculations by the way.

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!