topic Re: select all the value from the highest value in SAS Programming

select all the value from the highest value

soham_sas — Wed, 27 Jun 2018 13:15:22 GMT

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

Re: select all the value from the highest value

ybolduc — Wed, 27 Jun 2018 13:30:00 GMT

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?

Re: select all the value from the highest value

soham_sas — Wed, 27 Jun 2018 13:36:10 GMT

yes please .. if you can tell me the logic for how to code it then also it will work 🙂

Re: select all the value from the highest value

mkeintz — Wed, 27 Jun 2018 13:41:19 GMT

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.

Re: select all the value from the highest value

Kurt_Bremser — Wed, 27 Jun 2018 13:51:12 GMT

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

Re: select all the value from the highest value

Astounding — Wed, 27 Jun 2018 14:30:27 GMT

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;

Re: select all the value from the highest value

novinosrin — Wed, 27 Jun 2018 15:48:51 GMT

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;

Re: select all the value from the highest value

Ksharp — Thu, 28 Jun 2018 03:17:03 GMT

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;