topic How to I group Sales with sales person in SAS Programming

How to I group Sales with sales person

Cheggg1111 — Sat, 30 Oct 2021 22:18:59 GMT

I have a file of raw data(given below) and I am hoping to turn it in to the output (given below) by using a single data step.

Each observation contains only the following variables: the salesperson’s name (character type of length at least 20), the salesperson’s latest sales date (in SAS DATE numeric values), the number of days (standard numeric type) between the salesperson’s latest date of sales and the 30th of September 2021 (For example, if the latest sales date is 29/Sep/2021, then the number of days between the latest date of sales and the 30th of September 2021 is 1.), and the salesperson’s total sales (standard numeric type).

Raw Data:

David Wong,1/Sep/2021,13200

Brian Leung,15/Sep/2021,23450

Brian Leung,10/Sep/2021,33000

Mary Chan,3/Sep/2021,45600

Mary Chan,20/Sep/2021,37800

Mary Chan,1/Aug/2021,21500

Mary Chan,30/Aug/2021,42000

John Tam,12/Sep/2021,35000

Output:

How do I get the following output by only using one data step

Cheggg1111 — Sat, 30 Oct 2021 22:30:29 GMT

How to I get the output (given below) from the raw data (given) by only using one Data Step.

Raw Data:

David Wong,1/Sep/2021,13200

Brian Leung,15/Sep/2021,23450

Brian Leung,10/Sep/2021,33000

Mary Chan,3/Sep/2021,45600

Mary Chan,20/Sep/2021,37800

Mary Chan,1/Aug/2021,21500

Mary Chan,30/Aug/2021,42000

John Tam,12/Sep/2021,35000

Output:

Re: How do I get the following output by only using one data step

Patrick — Sat, 30 Oct 2021 23:32:23 GMT

Welcome to the SAS Forum.

Going forward please post your questions only once.

What have you tried already?

Here some pointers:

1. Your source data must be sorted by salesperson and date (if that's not the case the use Proc Sort)

2. The data must be read into SAS as a SAS Date value (use the appropriate SAS date informat in the input step, apply a SAS date format to the variable).

3. Use by salesperson name and first/last logic in the data step

- sales_tot+sales

- if last.salesperson then do; no_days=sales_date - '30sep2021'd;output; call missing(sales_tot); end;

You can find an answer with code here - but suggest you give this first a try on your own.

Re: How to I group Sales with sales person

Kurt_Bremser — Sun, 31 Oct 2021 08:29:38 GMT

I merged your double post.

See my answer in your classmate's thread (here).

Re: How do I get the following output by only using one data step

Astounding — Sun, 31 Oct 2021 06:30:18 GMT

This can be done (but not by me) in a single DATA step, using unsorted raw data, using a hash table that uses NAME as the key.

Results would be in alphabetical order.

Re: How do I get the following output by only using one data step

Kurt_Bremser — Mon, 01 Nov 2021 13:08:19 GMT

Do you have a SAS dataset or do you want to read an external file and do the calculations while reading it?