How to I group Sales with sales person

[Cheggg1111]

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:

[Cheggg1111]

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

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).

Output:

[Patrick]

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.

[Astounding]

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.

[Kurt_Bremser]

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

[Kurt_Bremser]

I merged your double post.

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