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
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;
post the expected output for the sample plz
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.
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.
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;
Is there any way that we can solve this using Group processing?
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.
Thank u .. I want my output to be like this .
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.