BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nidhi_vats
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20

post the expected output for the sample plz

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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.

Nidhi_vats
Calcite | Level 5

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.

novinosrin
Tourmaline | Level 20

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;
Nidhi_vats
Calcite | Level 5

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

ballardw
Super User

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.

 

Nidhi_vats
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 995 views
  • 0 likes
  • 4 in conversation