Retrieving rows by condition

[Pandu2]

Hi All,
I require some assistance from you all.
I've a table has 10 columns but I require output table based on 2 columns only they are sas_date and amount . And it has data of February to till date. In that I need to keep a condition like if the amount is max for a record then I need the overall rows where the sas_date should be older date.
Ex:
Sas_date x y z amount
09feb2022 1 2 3 1
01feb2022 1 2 3 5
20mar2022 1 2 3 2
11apr2022 6 6 7 8
24feb2022 6 6 7 9
25mar2022 6 6 7 9
17apr2022 9 8 6 1
19mar2022 9 8. 6. 1

Output table should be:
Sas_date. | X. Y. Z. Amount
01feb2022| 1 2. 3. 5
24feb 2022 6. 6. 7. 9
19mar2022 9. 8. 6 1
If the amount has all 1's for every row then the amount should be same but the sas date should be an older date.

Thanks.

[PaigeMiller]

In that I need to keep a condition like if the amount is max for a record then I need the overall rows where the sas_date should be older date.

I'm not understanding this. Please explain in more detail and walk us through an example step by step.

[Pandu2]

Hi, thanks for responding.
Could you please look at the example which I added

[PaigeMiller]

---

@Pandu2 wrote:
Hi, thanks for responding.
Could you please look at the example which I added

---

As far as I can see, you have not explained anything (which I specifically asked for), nor have you walked us through the example step-by-step (which I specifically asked for). So I still don't understand.

[Pandu2]

data have;

input id term project sasdate date1

cards;

1 10 197 01may2022 07feb2022

1 10 197 09apr2022
12mar2022

1 10 197 30apr2022
15apr2022

1 10 201 31mar2022
29mar2022

1 10 201 17feb2022
11jan2022

;
This is the data I've and what I require is sas_date should be greater than date1 and the sas_date should be the earliest date. Once the condition meets that row should be picked by grouping id term project.
Output table should be like :

1 10 197 09apr2022
12mar2022


1 10 201 17feb2022
11jan2022

;
I hope you got me.

[Kurt_Bremser]

Please review your code so that the step works without ERRORs or WARNINGs, then re-post it by copy/pasting the code into a code box opened with the "little running man" icon.

If you have trouble writing a simple data step, we should tackle this first before you dig deeper into SAS coding, so don't hesitate to ask how to write the step so that it works.

[Pandu2]

Please ignore the errors which I wrote in data step, but please do understand the requirements. Thankyou.

[Kurt_Bremser]

If you think you can fix your data step code yourself, do it. Right now I have no idea how your dates are stored.

[Pandu2]

Atleast, please do let me know how to find the earliest date in a bunch of date values. Thankyou.

[Kurt_Bremser]

Finding minimum values is done with the MIN Function in a data step, or the MIN Summary Function in SQL.

[Pandu2]

Can we find the earliest date by using min function with a date9 format . Please provide an example for it. Thankyou.

[Kurt_Bremser]

Do you have a numeric variable with a DATE9 format attached, or a character variable that contains the string as posted?

[Pandu2]

I've a date variable which is in date9 format.

[Kurt_Bremser]

You only have one date1 value in your data. Is this valid for the whole id, for id and term, or id, term and project? If the latter, which value is to be used for the second project?

[Pandu2]

I've two date variables
1. Sasdate
2.date1
And both these two are valid for whole Id,term, project variables.