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:
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 |
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.
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?
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.