Fill the grid using automation

[Deva_123]

Hi All, I need to fill the below grid by automation, Please advise me the best way to approach:

[Reeza]

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:

---

[Duggins]

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!

[Deva_123]

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; 

[Duggins]

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.

[Deva_123]

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;

[Reeza]

Did you try PROC TABULATE?

[Duggins]

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;