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!
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;
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.
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;
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.