topic Re: Get values of minimum date with a range in SAS Programming

Get values of minimum date with a range

udupa_13 — Tue, 09 Feb 2021 08:54:00 GMT

Data comprises of several columns including customer ID but need to pull data of only from the row with minimum date from (DATEVAL) for each customer, however this minimum date has to be within a range eg: within Dec'20 month. And finally sum the amount(AMT) of that customer for that month only.

Re: Get values of minimum date with a range

Phil_NZ — Tue, 09 Feb 2021 09:01:39 GMT

Could you please provide a small set of your sample data, it is quite ambiguous to me regarding your request?

Re: Get values of minimum date with a range

andreas_lds — Tue, 09 Feb 2021 09:08:40 GMT

The requirement "need to pull data of only from the row with minimum date" seems to contradict "sum the amount(AMT) of that customer for that month only", so please post data in usable form and what you expect as result.

Re: Get values of minimum date with a range

udupa_13 — Tue, 09 Feb 2021 09:09:33 GMT

ID - DATE - AMT- CITY- CODE
1-07SEP2020-10000-LONDON-PPS
1-11DEC2020-50000-BELARUS-PPK
2-06DEC2020-40000-COLOMBO-PPS
2-28JAN2021-30000-MUMBAI-PPL
2-02DEC2020-20000-MUNICH-PPS

I need to get this assuming my filter month is DEC 2020
ID-AMT:
1-0 (since minimum date for this customer is September)
2-20000

Re: Get values of minimum date with a range

udupa_13 — Tue, 09 Feb 2021 09:13:15 GMT

Sorry for the confusion.
Provided a scenario here: https://communities.sas.com/t5/SAS-Programming/Get-values-of-minimum-date-with-a-range/m-p/717816#M222049

Re: Get values of minimum date with a range

Shmuel — Tue, 09 Feb 2021 09:50:04 GMT

Does next code fits you requirements? If not - please explain what is the issue.

(coed was edited to fix bugs )

data have; 
  length city $12 month $7;
  input ID  DATE :date9. AMT  CITY $ CODE $;
  month = put(date, yymms7.);
cards;
1 07SEP2020 10000 LONDON  PPS
1 11DEC2020 50000 BELARUS PPK
2 06DEC2020 40000 COLOMBO PPS
2 28JAN2021 30000 MUMBAI  PPL
2 02DEC2020 20000 MUNICH  PPS
; run;

/* sort and select 1st month of ID */
proc sort data=have; by ID date; run;
data to_select;
 set have;
  by ID;
     if first.ID then output; /* or select by required month "yyyy/mm" */
     keep ID month;
run;
/* select data to deal with and sum amount */
proc sql;
   select a.ID, 
          sum(amt) as total
   from have as a 
   right join to_select as b 
   on a.ID=b.ID and a.month = b.month
   group by a.ID;
quit;

Re: Get values of minimum date with a range

udupa_13 — Tue, 09 Feb 2021 10:13:47 GMT

Data is already present in the server, so I need only the query to get the desired output without making a new dataset

Re: Get values of minimum date with a range

andreas_lds — Tue, 09 Feb 2021 10:23:35 GMT

@udupa_13 wrote:
Data is already present in the server, so I need only the query to get the desired output without making a new dataset

The step creating the dataset "have" is only part of the code, because you failed to provide the data in usable form.