topic Re: sum of sales for each city in SAS Programming

sum of sales for each city

BrahmanandaRao — Tue, 15 Jun 2021 06:37:10 GMT

data sales;
input city $  sales ;
datalines;
mumbai 2500
mumbai 1522
delhi  2000
delhi  3500
kolkatta 7500
kolkatta 8000
;
run;

proc sort data= sales;
by city sales;
run;

data s;
set sales;
by city;
if first.city then sales1=0;
sales+sales;
proc print;
run;





proc sql;
select city,sales ,sum(sales) as total
from sales
group by city,sales;
quit;

where I did wrong both datastep and proc sql

Re: sum of sales for each city

Kurt_Bremser — Tue, 15 Jun 2021 07:04:06 GMT

In your data step, you sum up the wrong variable, and you do not suppress the output for each incoming observation (hint: use a subsetting IF with LAST. You have been given NUMEROUS examples for this in your previous questions).

In your SQL, you group by sales, so the summary will still happen per each observation, not by city alone. Remove sales from the SELECT and the GROUP BY.

Re: sum of sales for each city

andreas_lds — Tue, 15 Jun 2021 09:48:46 GMT

I would not use a data step (or even proc sql), but proc summary to get the sum for each city.

proc summary data=sales nway;
  class City;
  var Sales;
  output out=city_sums(drop= _:) sum=;
run;

Re: sum of sales for each city

PaigeMiller — Tue, 15 Jun 2021 10:05:02 GMT

proc summary nway data=sales;
    class city; 
    var sales;
    output out=_sums_ sum=sum_sales;
run;

This would be a great time to start learning the very useful and powerful PROC SUMMARY!

No sorting needed. No writing your own SUM statements in a DATA step. And if you have another variable that has to be summed, you just add additional variable names into the VAR statement.

Re: sum of sales for each city

BrahmanandaRao — Tue, 15 Jun 2021 12:39:54 GMT

interviewer ask datastep how can you solve it so please correct where i did wrong

Re: sum of sales for each city

Kurt_Bremser — Tue, 15 Jun 2021 12:42:26 GMT

I already pointed out your mistake. Correct it.

Re: sum of sales for each city

PaigeMiller — Tue, 15 Jun 2021 12:51:22 GMT

You could tell the interviewer that PROC SUMMARY also solves the problem, and since you know how to do it in PROC SUMMARY you don't have to write custom DATA step code to do this.

Re: sum of sales for each city

BrahmanandaRao — Tue, 15 Jun 2021 13:02:05 GMT

data sales;
input city $ sales ;
datalines;
mumbai 2500
mumbai 1522
delhi 2000
delhi 3500
kolkatta 7500
kolkatta 8000
;
run;

proc sort data= sales;
by city ;
run;

data summ;
set sales;
by city;
if first.city then total_sales=0;
total_sales+ sales;
if last.city;
proc print;
run;
I got it

Re: sum of sales for each city

Sajid01 — Tue, 15 Jun 2021 13:12:02 GMT

Hello @BrahmanandaRao

I am posting the updated code for use with data step. (There could be other approaches too).

data sales;
input city $  sales ;
datalines;
mumbai 2500
mumbai 1522
delhi  2000
delhi  3500
kolkatta 7500
kolkatta 8000
;
run;

proc sort data= sales;
by city sales;
run;

data s (drop =sales);
set sales;
by city;
if first.city then sales1=0;
sales1+sales;
if last.city then output;
run;
proc print;
run;

Only the value of last.city has to be output as we are interested in the sum.
I have used Sales1 as the accumulator variable to hold the sum.

Re: sum of sales for each city

Kurt_Bremser — Tue, 15 Jun 2021 13:15:20 GMT

There is a very slight optimization that can be done:

data summ;
set sales;
by city;
if first.city
then total_sales = sales;
else total_sales + sales;
if last.city;
run;

At first.city, there will now be only one assignment instead of two.

Re: sum of sales for each city

ballardw — Tue, 15 Jun 2021 14:02:38 GMT

@BrahmanandaRao wrote:
interviewer ask datastep how can you solve it so please correct where i did wrong

My personal question for an interviewer when given a question like this would be to clarify exactly why a data step is needed.

Other approaches such as Proc SQL, Proc Summary/Means, Proc Tabulate and Proc Report will do the same thing, often with less headache depending on the specific sum(s) needed. So there should be a reason to be forced to use a data step solution.

Sometimes an interview question isn't so much can you do exactly what is mentioned but do you know enough to ask why an approach might be needed.

Re: sum of sales for each city

Reeza — Tue, 15 Jun 2021 14:18:17 GMT

I actually like this question as an interview question. It quickly demonstrates if a programmer understands a data step and by group functionality. It's definitely not something that's required but there are various reasons to do a running total or lagged data and this demonstrates both ideas.

Its also a very basic level of understanding and can easily verify if a programmers skill is at the level they claim during an interview process.

It's also kind of amusing when people don't even consider if their interviewers/testers are on the same forums and see the questions/answers.

Re: sum of sales for each city

BrahmanandaRao — Tue, 15 Jun 2021 15:46:01 GMT

Hi KurtBremser

if we are using sum statement instead of sum function we getting problem when missing values are there data

Re: sum of sales for each city

Reeza — Tue, 15 Jun 2021 16:39:53 GMT

Your example data does not demonstrate any missing values so it seems like it may not reflect your actual data. If that's the case please update your question with a more accurate data example.

Re: sum of sales for each city

PaigeMiller — Tue, 15 Jun 2021 16:45:17 GMT

I just felt I would mention this: PROC SUMMARY handles missing values appropriately, no extra coding needed.

Re: sum of sales for each city

Sajid01 — Tue, 15 Jun 2021 17:18:44 GMT

Hello All.
This topic is very interesting and there is a lot for me to learn.
I have a basic question, Please excuse me for my ignorance.
If the question has to be answered using data step only, can one use procs's for example proc means or proc sql ?

Re: sum of sales for each city

PaigeMiller — Tue, 15 Jun 2021 17:48:02 GMT

@Sajid01

I think the problem SHOULD be answered by PROC SUMMARY/PROC MEANS or PROC SQL. I don't think people should program their own sums or means or other basic statistics by group in a DATA step. We have seen, in the SAS Communities, over and over again, people (usually with just a little experience in SAS) struggle to do this in a DATA step, and get it wrong, or they write code that doesn't work, or they write code that mis-handles missing values. Why spend the time writing DATA step code, debugging it, verifying it works, when SAS has already done this in PROC SUMMARY (and it works properly with missing values)???

Re: sum of sales for each city

Reeza — Tue, 15 Jun 2021 18:16:51 GMT

"If the question has to be answered using data step only" is a an artificial limit that only exists in non work like situation, such as homework, class exams or tests before a job. And IMO, those situations are typically one where a user should be able to answer the question themselves without significant assistance of external users.

Re: sum of sales for each city

Kurt_Bremser — Tue, 15 Jun 2021 18:31:04 GMT

@BrahmanandaRao wrote:

Hi KurtBremser

if we are using sum statement instead of sum function we getting problem when missing values are there data

Maxim 4 (short version): Try It.