topic Trying to format my final table in a very specific format in New SAS User

Trying to format my final table in a very specific format

CSB35 — Thu, 04 Apr 2024 20:52:25 GMT

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.

Re: Trying to format my final table in a very specific format

PaigeMiller — Thu, 04 Apr 2024 21:16:07 GMT

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).

Re: Trying to format my final table in a very specific format

CSB35 — Thu, 04 Apr 2024 21:18:04 GMT

Thank you for the valuable information! Here is a screen capture

Re: Trying to format my final table in a very specific format

CSB35 — Thu, 04 Apr 2024 21:21:39 GMT

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;

Re: Trying to format my final table in a very specific format

PaigeMiller — Thu, 04 Apr 2024 21:23:41 GMT

I also requested sample data, provided as working SAS data step code (examples and instructions).

Re: Trying to format my final table in a very specific format

CSB35 — Thu, 04 Apr 2024 21:30:46 GMT

I have some of my code in an earlier reply, here is one of the four tables. They all have a similar format

Re: Trying to format my final table in a very specific format

ballardw — Thu, 04 Apr 2024 22:01:01 GMT

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-data-AKA-generate/ta-p/258712 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.