BookmarkSubscribeRSS Feed
Deva_123
Calcite | Level 5

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

 

Grid.PNG

7 REPLIES 7
Reeza
Super User

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:

 

Grid.PNG




Duggins
Obsidian | Level 7

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
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

My Input data looks like below, I am unable to automate the code. Please help me:

 

Customer IDTotal_SalesYEARSales_Date
1794.68201713Oct2017
26201.00201607Apr2016
32000.00201614Sep2016
42000.00201614Oct2016
52000.00201615Sep2016
61000.00201830Mar2018
7500.00201901Apr2019
81500.00201603Apr2016
9750.00201623Mar2016
10500.00201613Sep2016
11500.00201611Sep2016
122000.00201616Sep2016
132021.00201609Aug2016
14500.00201615Apr2016
15882.00201815Oct2018
16750.00201919Jul2019
17750.00201903Mar2019
18499288.00201624Aug2016
19500.00201618Apr2016
20500.00201617Oct2016

 

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
Super User
Did you try PROC TABULATE?
Duggins
Obsidian | Level 7

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 7 replies
  • 650 views
  • 0 likes
  • 3 in conversation