topic Re: Filter on the maximum of two ID's for each Date in SAS Programming

Filter on the maximum of two ID's for each Date

Alex28 — Wed, 13 Mar 2019 14:38:02 GMT

Hello everyone,

I am stuck at the following issue:
I want to filter my Data on two Dates ('31Dec2018'd,'30Nov2018'd), but I only need of each the maximum Version_Number AND then coming from this the maximum Result_ID.

Example:
Result_ID Version_Number Dates arbitrary_other_variables

3 2 29Dec2018 ...

1 1 30Nov2018 ...

5 4 31Dec2018 ...

6 4 30Nov2018 ...

10 8 31Dec2018 ...

Now the Output should be: First filter on the relevant Dates, then pick only those rows, which have the highest Version_Number (at first) and then the highest Result ID. Output should now look like this:

Result_ID Version_Number Dates arbitrary_other_variables

6 4 30Nov2018 ...

10 8 31Dec2018 ...

So Far, I always need to look up on my self the highest Version number and then coming from this the highest Result ID (which takes a high effort due to great amount of Data ) and enter this data in my code:

proc tabulate data=rc_res.creditrisk;

var bar_obl_amt zus_obl_amt;
class date_dt;
WHERE date_DT IN
('31Dec2018'd,
'30Nov2018'd) AND AND VERSION_NR IN
(4,8) AND RESULT_ID IN
(6,10)
TABLE
(ZUS_OBL_AMT BAR_OBL_AMT),
date_dt *( Sum={LABEL="Summe"} )
all = 'Summe' *( Sum={LABEL="Summe"} ) ;

run;

Is there any code with max or something else which would make it automatically ? Could you please help? I appreciate it a lot and I am really thankful for everyone trying to help me.

Kind Regards

Alex

Re: Filter on the maximum of two ID's for each Date

ballardw — Wed, 13 Mar 2019 15:26:47 GMT

Something like this perhaps?

data have;
   informat dates date9.;
   format dates date9.;
   input Result_ID    Version_Number    Dates     ;
datalines;
3    2    29Dec2018  
1    1    30Nov2018  
5    4    31Dec2018  
6    4    30Nov2018  
10   8    31Dec2018  
;
run;

Proc sort data=have (where=( dates in ('30NOV2018'd '31DEC2018'd)))
    out=temp;
    by descending version_number;
run;
data want;
   set temp (obs=2);
run;

Please not the use of 1) a data step to provide example data. That way we have data to test code with and 2) posting the code in a code box opened with the forum's {I} to preserve formatting. The main message windows here will reformat text removing white space. Also the code box is appropriate for posting LOG entries as many error messages include indicators where the error was found by SAS but the message windows will move those characters making the log less helpful.

Re: Filter on the maximum of two ID's for each Date

PeterClemmensen — Wed, 13 Mar 2019 15:27:07 GMT

Hi and welcome to the SAS communities 🙂

Here is one approach

data have;
input Result_ID Version_Number Date:date9.;
format date date9.;
datalines;
3 2 29Dec2018
1 1 30Nov2018
5 4 31Dec2018
6 4 30Nov2018
10 8 31Dec2018
;

proc sql;
   create table want as
   select * from have
   where Date in ('30Nov2018'd, '31Dec2018'd)
   group by Date
   having Version_Number=max(Version_Number);
quit;

proc print data=want;
run;

Re: Filter on the maximum of two ID's for each Date

Alex28 — Wed, 13 Mar 2019 15:58:41 GMT

Thank you very much for your help and sorry for my mistakes

Re: Filter on the maximum of two ID's for each Date

Alex28 — Wed, 13 Mar 2019 16:17:23 GMT

WOW, thank you :)))