topic Re: Find the max and unique value by index in SAS Programming

Find the max and unique value by index

lpy0521 — Tue, 11 Sep 2018 18:41:15 GMT

I am trying to find: for each same rownum, if check=1 and is the only 1 for that rownum, then output =1, otherwise output =0.

Here below is a data example:

Data have

rownum	check
6	0.66667
6	0.81818
7	0.5
7	1
7	0.75
8	0.5
8	0.75
28	1
28	1

Data want:

rownum	check	output
6	0.66667	0
6	0.81818	0
7	0.5	0
7	1	1
7	0.75	0
8	0.5	0
8	0.75	0
28	1	0
28	1	0

Please let me know how to get the desired output, thanks!

Re: Find the max and unique value by index

mkeintz — Tue, 11 Sep 2018 18:51:29 GMT

If the data are sorted by rownum:

data want (drop=n_ones);
  set have (where=(check=1) in=hasones)
      have (in=inall);
  by rownum;
  if first.rownum then n_ones=0;
  n_ones+hasones;
  if inall;
  output=(check=1 and n_ones=1);
run;

For each rownum this data step (1) reads and counts all instances of check=1, (2) reads all records (including re-reading the check=1 recs) for the rownum. It only keeps records from the second group, but at that point it is possible to retrieve the count of ones and set the output variable.

Re: Find the max and unique value by index

novinosrin — Tue, 11 Sep 2018 18:57:48 GMT

data have;
input rownum	check;
cards;
6	0.66667
6	0.81818
7	0.5
7	1
7	0.75
8	0.5
8	0.75
28	1
28	1
;

proc sql;
create table want as
select *,(sum(check=1)=1)*(check=1) as output
from have
group by rownum;
quit;