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
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.