BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lpy0521
Fluorite | Level 6

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

rownumcheck
60.66667
60.81818
70.5
71
70.75
80.5
80.75
281
281

 

Data want:

rownumcheckoutput
60.666670
60.818180
70.50
711
70.750
80.50
80.750
2810
2810

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

2 REPLIES 2
mkeintz
PROC Star

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.

--------------------------
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

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1549 views
  • 0 likes
  • 3 in conversation