BookmarkSubscribeRSS Feed
hjjijkkl
Pyrite | Level 9

have a data and I want an outcome where the first cnt is ‘1’

ID

visit

Cnt

1

5/3/20

0

1

7/2/20

0

2

8/9/21

1

3

1/5/20

0

3

4/18/20

1

3

8/23/20

1

4

6/8/20

0

4

10/9/20

1

 

 

I want an output like this

ID

visit

cnt

2

7/2/20

1

3

4/18/20

1

4

10/9/20

1

 

4 REPLIES 4
ballardw
Super User

Does your CNT variable ever decrease after a 1 is encountered?

If not you may be able to use (untested because the example data is not nice)

data want;
    set have;
    by id cnt;
    if first.cnt and cnt=1;
run;
hjjijkkl
Pyrite | Level 9
CNT is either 1 or 0.
ballardw
Super User

@hjjijkkl wrote:
CNT is either 1 or 0.

The question is does CNT ever go to 0 after it is 1.

The proposed possible solution using BY group processing and First/ Last processing will not work if you can have something like:

 

ID   CNT

1   0

1  0

1  1

1  1

1  0

1  0

1 1

 

because BY requires a specified order, as shown assumes increasing, and would generate a "not sorted by BY variables" error.

Other methods would be needed if the data might have the out of order CNT values.

Ksharp
Super User
data have;
input 
ID 
visit : mmddyy10. 
Cnt;
format visit mmddyy10.;
cards;
1
5/3/20
0
1
7/2/20
0
2
8/9/21
1
3
1/5/20
0
3
4/18/20
1
3
8/23/20
1
4
6/8/20
0
4
10/9/20
1
;


data want;
 set have;
 by id;
 retain found .;
 if first.id then found=.;
 if not found and cnt=1 then do;output;found=1;end;
 drop found;
run;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1035 views
  • 0 likes
  • 3 in conversation