topic Re: Calculate the highest two records of Metrc2 for each date in SAS Programming

Calculate the highest two records of Metrc2 for each date

Nidhi_vats — Wed, 19 Sep 2018 19:57:32 GMT

I have a Data like this and i want to calculate the two highest records for every date

Data tempdata;

Input obs Date $ Age Salary Metric2;
Datalines;
1 JAN1999 56 78 14
2 FEB1999 32 109 14
3 FEB1999 35 106 14
4 FEB1999 47 115 22
5 MAY1999 55 121 31
6 JUN1999 61 114 31
7 JUL1999 67 102 72
8 AUG1999 64 76 72
9 JUL1999 78 77 54
10 JUL1999 81 62 47
11 JUL1999 84 31 52
12 JUL1999 2 44 55
13 JAN2000 37 91 83
14 FEB2000 41 102 27
15 FEB2000 52 98 19
16 FEB2000 61 118 22
17 FEB2000 49 88 29
18 FEB2000 24 101 54
19 JUL2000 45 91 69
20 JUL2000 63 65 53
21 SEP2000 60 49 68
22 JUL2000 78 70 41
23 JUL2000 82 44 58
24 JUL2000 93 57 47
;

Firstly i have converted the date column using monyy7. format then i have tried this code

Data Ques3;
Set Tempdata1;
By Date1;
Retain MaxM ;
IF First.Date1=1 THEN MaxM = MAX( Metric2, MaxM);
Else MaxM= Metric2;
If Last.Date1=1;
Run;

But this code gives me only the highest values for every date

Plz suggest

Re: Calculate the highest two records of Metrc2 for each date

novinosrin — Wed, 19 Sep 2018 20:12:52 GMT

post the expected output for the sample plz

Re: Calculate the highest two records of Metrc2 for each date

VDD — Wed, 19 Sep 2018 20:15:53 GMT

your output is because you did not tell the process to output the records.

only the last record for the date, where Last.Date1=1; allow the record to be recorded.

Re: Calculate the highest two records of Metrc2 for each date

Nidhi_vats — Wed, 19 Sep 2018 20:27:11 GMT

My code gives me the higest value of matric2 for every date. But i want two highest values for every date. Like for FEB1999 it should gives the values 22 and 14.

Re: Calculate the highest two records of Metrc2 for each date

novinosrin — Wed, 19 Sep 2018 20:32:25 GMT

see if this helps

Data tempdata;

Input obs Date $ Age Salary Metric2;
Datalines;
1 JAN1999 56 78 14
2 FEB1999 32 109 14
3 FEB1999 35 106 14
4 FEB1999 47 115 22
5 MAY1999 55 121 31
6 JUN1999 61 114 31
7 JUL1999 67 102 72
8 AUG1999 64 76 72
9 JUL1999 78 77 54
10 JUL1999 81 62 47
11 JUL1999 84 31 52
12 JUL1999 2 44 55
13 JAN2000 37 91 83
14 FEB2000 41 102 27
15 FEB2000 52 98 19
16 FEB2000 61 118 22
17 FEB2000 49 88 29
18 FEB2000 24 101 54
19 JUL2000 45 91 69
20 JUL2000 63 65 53
21 SEP2000 60 49 68
22 JUL2000 78 70 41
23 JUL2000 82 44 58
24 JUL2000 93 57 47
;

proc rank data=tempdata out=want(where=(Metric2rank in (1,2))) ties=low descending;
	
   by Date notsorted;
 
   var Metric2;
   ranks Metric2rank ;
run;

Re: Calculate the highest two records of Metrc2 for each date

Nidhi_vats — Wed, 19 Sep 2018 20:37:37 GMT

Is there any way that we can solve this using Group processing?

Re: Calculate the highest two records of Metrc2 for each date

ballardw — Wed, 19 Sep 2018 20:43:14 GMT

One way with a data set, different sort order. I made the dates actual date values so the sort order makes sense with them.

Data tempdata;

Input obs Date :monyy7. Age Salary Metric2;
format date monyy7.;
Datalines;
1 JAN1999 56 78 14
2 FEB1999 32 109 14
3 FEB1999 35 106 14
4 FEB1999 47 115 22
5 MAY1999 55 121 31
6 JUN1999 61 114 31
7 JUL1999 67 102 72
8 AUG1999 64 76 72
9 JUL1999 78 77 54
10 JUL1999 81 62 47
11 JUL1999 84 31 52
12 JUL1999 2 44 55
13 JAN2000 37 91 83
14 FEB2000 41 102 27
15 FEB2000 52 98 19
16 FEB2000 61 118 22
17 FEB2000 49 88 29
18 FEB2000 24 101 54
19 JUL2000 45 91 69
20 JUL2000 63 65 53
21 SEP2000 60 49 68
22 JUL2000 78 70 41
23 JUL2000 82 44 58
24 JUL2000 93 57 47
;
run;

proc sort data=tempdata;
   by date descending metric2;
run;
data want;
  set tempdata;
  by date descending metric2;
  retain flag;
  if first.date then flag=1;
  else flag+1;
  if flag le 2;
  drop flag;
run;

There are some details you may want to address. Such as if you have 3 identical metric2 values for a date. Do you want 1 (unique value of metric) 2 (ordered) or 3 (keep ties) in the output. Or if the second largest value has a tie with the third.

Re: Calculate the highest two records of Metrc2 for each date

Nidhi_vats — Wed, 19 Sep 2018 20:49:57 GMT

Thank u .. I want my output to be like this .