Hi All, I need to fill the below grid by automation, Please advise me the best way to approach:
PROC REPORT or TABULATE.
@Deva_123 wrote:
Hi All, I need to fill the below grid by automation, Please advise me the best way to approach:
What does the data look like that you are working with and what do you want the final result to look like? That would be helpful for suggesting a solution. For example, if the values of sales are already present along with the full dates, that is different than if you need to create the dates and the sums!
Hi dugginsj, My final result should be the grid as shown, my data set has Sales date and Sales Amount. Just I need to sum up by date.
I can create values by below SQL but looking for automation :
PROC SQL;
CREATE TABLE Grid AS
SELECT
/* SUM_of_Tot_Sales */
(SUM(t1.Tot_Sales)) FORMAT=15.2 AS SUM_of_Tot_Sales
FROM Sales t1
WHERE t1.YEAR = 2018 AND t1.Sales-Date <= '1Jan2019'd;
QUIT;
From the SQL code, it appears Year is already a variable in your data set, but your rows also look to depend on the month as well. If those are both variables in your data set, then you can use PROC REPORT to calculate the sums you want, do any grouping you need, and create columns out of the Years if you include them as an ACROSS variable.
I will say though, that depending on the complexity of the report relative to the data you want - it might be easier to use an analysis PROC like PROC MEANS to get the summaries you want into a data set, then just use PROC REPORT to customize the display for your data.
My Input data looks like below, I am unable to automate the code. Please help me:
Customer ID | Total_Sales | YEAR | Sales_Date |
1 | 794.68 | 2017 | 13Oct2017 |
2 | 6201.00 | 2016 | 07Apr2016 |
3 | 2000.00 | 2016 | 14Sep2016 |
4 | 2000.00 | 2016 | 14Oct2016 |
5 | 2000.00 | 2016 | 15Sep2016 |
6 | 1000.00 | 2018 | 30Mar2018 |
7 | 500.00 | 2019 | 01Apr2019 |
8 | 1500.00 | 2016 | 03Apr2016 |
9 | 750.00 | 2016 | 23Mar2016 |
10 | 500.00 | 2016 | 13Sep2016 |
11 | 500.00 | 2016 | 11Sep2016 |
12 | 2000.00 | 2016 | 16Sep2016 |
13 | 2021.00 | 2016 | 09Aug2016 |
14 | 500.00 | 2016 | 15Apr2016 |
15 | 882.00 | 2018 | 15Oct2018 |
16 | 750.00 | 2019 | 19Jul2019 |
17 | 750.00 | 2019 | 03Mar2019 |
18 | 499288.00 | 2016 | 24Aug2016 |
19 | 500.00 | 2016 | 18Apr2016 |
20 | 500.00 | 2016 | 17Oct2016 |
I am unable to automate the code :
PROC SQL;
CREATE TABLE WORK.'QUERY_FOR_SAMPLE DATA_0000'n AS
SELECT /* COUNT_of_Customer ID */
(COUNT(t1.'Customer ID'n)) AS 'COUNT_of_Customer ID'n,
/* SUM_of_Total_Sales */
(SUM(t1.Total_Sales)) FORMAT=F12.2 AS SUM_of_Total_Sales,
t1.YEAR
FROM WORK.'QUERY_FOR_SAMPLE DATA'n t1
WHERE t1.Sales_Date <= '1Jan2017'd AND t1.YEAR = 2016
GROUP BY t1.YEAR;
QUIT;
I would strongly suggest looking for an alternative to SQL. Your table is a bit confusing because the rows show "Y+1" which makes it appear as if they would span multiple years, but your columns indicate the years and your row label makes it appear your sums should be cumulative rather than just a sum within each month*year combination.
If you truly want cumulative sums without necessarily having data for each of the month*year combinations, then you may need to augment your data set to include every month*year combination before computing the sums, otherwise SAS will not include rows/columns where no data appears in the data set.
If this were my client, I would want a more clear understanding of exactly how those values are supposed to appear in the table. Then I would go to the DATA step and build out what I needed to get my sums (for example, I included a DATA step below), then I would go to PROC REPORT and print them. It isn't a single-step solution, but your data does not appear to be in a format that would allow us to provide a single step that gets exactly the output you want.
data want(drop = id sales);
set have;
by year month date;
if first.year then total = 0;
total+sales;
if last.month then output;
run;
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 16. Read more here about why you should contribute and what is in it for you!
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.