Hi
I have a table like below (as table Have) , we want a table (like want)
first i have to select the highest servnum for a cntnum (which is 6564 for cntnum 153787)
now the cate for the highest cntnum is 5 , so in the final output we have to select all the lines where the cate is less than equal to 5 (i.e 5,4,3,2 for this case)
NOTE: the cate 5 is repeated here for cntnum 153787 in that case we need to select the line where the servnum is the highest (that is we need to select the first row)
data have
cntnum | servnum | linenum | cate |
153787 | 6564 | 6563 | 5 |
153787 | 6563 | 6390 | 4 |
153787 | 6562 | 6561 | 6 |
153787 | 6561 | 5934 | 5 |
153787 | 6560 | 6559 | 6 |
153787 | 6559 | 4532 | 5 |
153787 | 6558 | 1430 | 6 |
153787 | 6557 | 3768 | 5 |
153787 | 6556 | 6555 | 6 |
153787 | 6555 | 6554 | 5 |
153787 | 6554 | 6553 | 4 |
153787 | 6553 | 6552 | 3 |
153787 | 6552 | 1 | 2 |
256434 | 198 | 197 | 3 |
256434 | 197 | 1 | 2 |
256434 | 195 | 194 | 5 |
256434 | 194 | 193 | 4 |
256434 | 193 | 192 | 3 |
256434 | 192 | 1 | 2 |
data want
cntnum | servnum | linenum | cate |
153787 | 6564 | 6563 | 5 |
153787 | 6563 | 6390 | 4 |
153787 | 6553 | 6552 | 3 |
153787 | 6552 | 1 | 2 |
256434 | 198 | 197 | 3 |
256434 | 197 | 1 | 2 |
Are you trying to say that you need us to write a piece of code for you that would select the record with the highest servnum for each (cntnum, cate) pair?
yes please .. if you can tell me the logic for how to code it then also it will work 🙂
Let's see if I understand your task. Within each CNTNUM you want one record for each CATE value that is equal to or less than the CATE that corresponds to the highest observed SERVNUM in that CNTNUM. And the record to be chosen for each CATE is to be the one with the highest SERVNUM.
Because your data are apparently sorted by descending servnum within each cntnum group, it's relatively straightforward. The reference value for CATE is always going to be the first record of that CNTNUM.
data have;
input cntnum servnum linenum cate ;
datalines;
153787 6564 6563 5
153787 6563 6390 4
153787 6562 6561 6
153787 6561 5934 5
153787 6560 6559 6
153787 6559 4532 5
153787 6558 1430 6
153787 6557 3768 5
153787 6556 6555 6
153787 6555 6554 5
153787 6554 6553 4
153787 6553 6552 3
153787 6552 1 2
256434 198 197 3
256434 197 1 2
256434 195 194 5
256434 194 193 4
256434 193 192 3
256434 192 1 2
run;
data want (drop=_:);
array _found{20};
do until (last.cntnum);
set have;
by cntnum descending servnum;
if first.cntnum then _cate1=cate;
if cate<=_cate1 and _found{cate}^=1 then do;
output;
putlog cntnum= servnum= linenum= cate=;
_found{cate}=1;
end;
end;
run;
Just make sure that the size of the array _FOUND is at least the level of the highest possible CATE value. Because of the sort order of records, you know that the first instance of any qualified CATE value (i.e. CATE <= _CATE1) is the desired record, at which point you can output the data and record that fact in the _FOUND array.
Retain a reference value, and output only in case the current cate is below that. To make it start at 5, initialize to 6.
data have;
infile cards dlm='09'x;
input cntnum servnum linenum cate;
cards;
153787 6564 6563 5
153787 6563 6390 4
153787 6562 6561 6
153787 6561 5934 5
153787 6560 6559 6
153787 6559 4532 5
153787 6558 1430 6
153787 6557 3768 5
153787 6556 6555 6
153787 6555 6554 5
153787 6554 6553 4
153787 6553 6552 3
153787 6552 1 2
256434 198 197 3
256434 197 1 2
256434 195 194 5
256434 194 193 4
256434 193 192 3
256434 192 1 2
;
run;
proc sort data=have; /* sort just to be sure */
by cntnum descending servnum;
run;
data want;
set have;
by cntnum;
retain lastcate;
if first.cntnum then lastcate = 6;
if cate < lastcate
then do;
lastcate = cate;
output;
end;
drop lastcate;
run;
proc print data=want noobs;
run;
(note how I put the data into a data step, so that it is easy to recreate the dataset with copy/paste and submit)
The result matches your want example:
cntnum servnum linenum cate 153787 6564 6563 5 153787 6563 6390 4 153787 6553 6552 3 153787 6552 1 2 256434 198 197 3 256434 197 1 2
A similar idea, but without a hard-coded maximum:
proc sort data=have;
by cntnum descending servnum;
run;
data want;
set have;
by cntnum;
if first.cntnum then cutoff = cate;
if cate <= cutoff then do;
output;
cutoff = cate - 1;
end;
retain cutoff;
drop cutoff;
run;
data have;
input cntnum servnum linenum cate ;
datalines;
153787 6564 6563 5
153787 6563 6390 4
153787 6562 6561 6
153787 6561 5934 5
153787 6560 6559 6
153787 6559 4532 5
153787 6558 1430 6
153787 6557 3768 5
153787 6556 6555 6
153787 6555 6554 5
153787 6554 6553 4
153787 6553 6552 3
153787 6552 1 2
256434 198 197 3
256434 197 1 2
256434 195 194 5
256434 194 193 4
256434 193 192 3
256434 192 1 2
run;
proc sql;
create table want as
select a.*
from
have a,(select cntnum,servnum, cate from have group by cntnum having servnum=max(servnum)) b
where (a.cntnum =b.cntnum and a.cate<b.cate) or (a.cntnum=b.cntnum and a.servnum=b.servnum and a.cate=b.cate)
group by a.cntnum,a.cate
having servnum=max(servnum)
order by cntnum,cate desc;
quit;
If I understood your question.
data have;
input cntnum servnum linenum cate ;
datalines;
153787 6564 6563 5
153787 6563 6390 4
153787 6562 6561 6
153787 6561 5934 5
153787 6560 6559 6
153787 6559 4532 5
153787 6558 1430 6
153787 6557 3768 5
153787 6556 6555 6
153787 6555 6554 5
153787 6554 6553 4
153787 6553 6552 3
153787 6552 1 2
256434 198 197 3
256434 197 1 2
256434 195 194 5
256434 194 193 4
256434 193 192 3
256434 192 1 2
run;
proc sort data=have out=temp ;
by cntnum servnum cate;
run;
data temp1;
set temp;
by cntnum ;
if last.cntnum ;
keep cntnum cate;
run;
data temp2;
merge temp temp1(rename=(cate=_cate));
by cntnum;
if cate le _cate;
drop _cate;
run;
proc sort data=temp2 out=temp3 ;
by cntnum cate;
run;
data want;
set temp3;
by cntnum cate;
if last.cate ;
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.