topic Re: Retrieving rows by condition in New SAS User

Retrieving rows by condition

Pandu2 — Fri, 29 Apr 2022 11:46:42 GMT

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.

Re: Retrieving rows by condition

PaigeMiller — Fri, 29 Apr 2022 11:54:59 GMT

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.

Re: Retrieving rows by condition

Pandu2 — Fri, 29 Apr 2022 12:09:23 GMT

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

Re: Retrieving rows by condition

PaigeMiller — Fri, 29 Apr 2022 12:12:02 GMT

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

Re: Retrieving rows by condition

Pandu2 — Wed, 04 May 2022 08:59:42 GMT

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.

Re: Retrieving rows by condition

Kurt_Bremser — Wed, 04 May 2022 13:25:25 GMT

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.

Re: Retrieving rows by condition

Pandu2 — Thu, 05 May 2022 01:32:34 GMT

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

Re: Retrieving rows by condition

Kurt_Bremser — Thu, 05 May 2022 04:53:43 GMT

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

Re: Retrieving rows by condition

Pandu2 — Thu, 05 May 2022 10:26:13 GMT

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

Re: Retrieving rows by condition

Kurt_Bremser — Thu, 05 May 2022 10:44:35 GMT

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

Re: Retrieving rows by condition

Pandu2 — Thu, 05 May 2022 11:10:39 GMT

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

Re: Retrieving rows by condition

Kurt_Bremser — Thu, 05 May 2022 11:12:56 GMT

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

Re: Retrieving rows by condition

PaigeMiller — Thu, 05 May 2022 12:05:46 GMT

@Pandu2 wrote:
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.

I still don't see you walking us through, step-by-step, how you arrive at the desired output (which I have previously requested). So I don't understand.

Re: Retrieving rows by condition

Pandu2 — Thu, 05 May 2022 14:24:58 GMT

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

Re: Retrieving rows by condition

Kurt_Bremser — Thu, 05 May 2022 14:52:12 GMT

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?

Re: Retrieving rows by condition

Pandu2 — Thu, 05 May 2022 16:25:23 GMT

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

Re: Retrieving rows by condition

Pandu2 — Fri, 06 May 2022 06:40:51 GMT

I apologise, I tried my best to walk you through this. Even if you still don't understand, can you atleast help me on comparing two sas date variables which are in date9 format. Thankyou.
The comparison should be like sas_date >= date1