BookmarkSubscribeRSS Feed
soham_sas
Quartz | Level 8

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

cntnumservnumlinenumcate
153787656465635
153787656363904
153787656265616
153787656159345
153787656065596
153787655945325
153787655814306
153787655737685
153787655665556
153787655565545
153787655465534
153787655365523
153787655212
2564341981973
25643419712
2564341951945
2564341941934
2564341931923
25643419212

 

data want 

cntnumservnumlinenumcate
153787656465635
153787656363904
153787655365523
153787655212
2564341981973
25643419712

 

 

 

 

 

7 REPLIES 7
ybolduc
Quartz | Level 8

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?

soham_sas
Quartz | Level 8

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Kurt_Bremser
Super User

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 
Astounding
PROC Star

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;

novinosrin
Tourmaline | Level 20
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;
Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 970 views
  • 0 likes
  • 7 in conversation