Get values of minimum date with a range

[udupa_13]

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.

[Phil_NZ]

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

[udupa_13]

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

[Shmuel]

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;

[udupa_13]

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

[andreas_lds]

---

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

[andreas_lds]

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.

[udupa_13]

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#M2...