I am creating a budget and want to format the finished product in one table like the one pictured. I have created four tables with one row and three columns each. I want SAS to output the data as one table in the format pictured in the attached file if that is possible.
I have following data from the four tables with one row and three columns each and need present the following four things:
- Current month budgeted expenses vs actual expenses and variance between the two
- Current month actual expenses from the prior year compared to current month actual expenses for the current year and variance between the two
- Year to Date Budgeted vs actual expenses for the current year and variance between the two
- Year to Date Budgeted expenses from the prior year compared to year to date expenses and variance between the two
Each table would look something like this: Budgeted Month Expenses, Actual Month Expenses, Variance
I need to format them into one table like the one attached for presentation purposes.
Many of us refuse to download Microsoft Office files, as they can be a security threat. If you want to show us the layout of a table you would like, make a screen capture and include it in your reply using the "Insert Photos" icon.
Please also provide sample data, as working SAS data step code (examples and instructions).
Thank you for the valuable information! Here is a screen capture
Here is the code I used to generate the four tables:
/*Month CY Budget and Actual*/ PROC SQL; CREATE Table WORK.Month_CYBudtoActuals as SELECT Sum('Budgeted Month'n) as CYBudMonth, Sum('Monthly Actuals'n) as CYActMonth FROM WORK.CYBUDGETTOCYACTUALS Quit; PROC SQL; CREATE Table WORK.Month_CYBudtoActuals as SELECT CYBudMonth, CYActMonth, (CYBudMonth - CYActMonth) as '$ Variance'n FROM WORK.Month_CYBudtoActuals Quit; /*Month PY Actual, CY Actual*/ PROC SQL; CREATE Table WORK.Month_PYtoCYActuals as SELECT Sum('Month PY Actuals'n) as 'PY Month Actual'n, Sum('CY Actuals'n) as 'CY Month Actual'n FROM WORK.PYACTUALSTOCYACTUALS Quit; PROC SQL; CREATE TABLE Work.Month_PYtoCYActuals as SELECT 'PY Month Actual'n, 'CY Month Actual'n, ('PY Month Actual'n - 'CY Month Actual'n) as '$ Variance'n FROM WORK.Month_PYtoCYActuals Quit; /*YTD CY bud and act*/ PROC SQL; CREATE Table WORK.YTD_CYBudtoActual as SELECT Sum('Budgeted Month'n) as 'CY YTD Bud'n, Sum('Monthly Actuals'n) as 'CY YTD Actual'n FROM WORK.CYBUDGETTOACTUALS_YTD Quit; PROC SQL; CREATE Table WORK.YTD_CYBudtoActual as SELECT 'CY YTD Bud'n, 'CY YTD Actual'n, ('CY YTD Bud'n - 'CY YTD Actual'n) as '$ Variance'n FROM WORK.YTD_CYBudtoActual Quit; /*YTD PY act. vs CY act.*/ PROC SQL; CREATE table WORK.YTD_PYtoCY_Actuals as SELECT Sum('PY YTD Actuals'n) as 'PY YTD Actual'n, Sum('CY Actuals'n) as 'CY YTD Actual'n FROM WORK.PYTOCY_ACTUALS_YTD Quit; PROC SQL; CREATE table WORK.YTD_PYtoCY_Actuals as SELECT 'PY YTD Actual'n, 'CY YTD Actual'n, ('PY YTD Actual'n - 'CY YTD Actual'n) as '$ Variance'n FROM WORK.YTD_PYtoCY_Actuals Quit;
I also requested sample data, provided as working SAS data step code (examples and instructions).
I have some of my code in an earlier reply, here is one of the four tables. They all have a similar format
Data means something that we can run code with.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.
Since your code is using multiple different source data sets then you would need to provide examples of each.
You also don't provide much guidance, as in any, as to what value goes where. Building a table should reference what variable(s) are used to build which column and/or row.
I have no way to tell from your picture showing a desired column headings MTD and YTD (NOT variables used anywhere in your code) or where "2024 Budget" and "2024 Actual" come from. I can make some sort of educated guesses because I've been programming for a while. But I also know from that experience that "guessing" is a very poor way to set rules for coding.
One has suspicion that this is part way through a process that split up an existing data set to get some summary values and now is looking to get put back together for the report. If this is indeed the case, then it may be better idea to provide an example of that earlier data set.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.