SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

create a flag for first non-zero observation by group

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

create a flag for first non-zero observation by group

[ Edited ]

Hi, I am not sure if this question has been asked, and I am new to SAS, so any help would be appriciated.

 

I have a table like the following:

ID Value

1       0

1       100

1       200

1       0

2       0

2       0

2       0

2       100

2       0

3       0

3       100

3       200

 

And I would like to create a flag for the first non-zero values for each ID. The resulting table will be:

ID   Value    Flag

1       0           0

1       100       1

1       200       0

1       0           0

2       0           0

2       0           0

2       0           0

2       100       1

2       0           0

3       0           0

3       100       1

3       200       0

 

Note that the observations are already sorted by ID and then Date, so I don't want to change the order of the observations. Is there any easier way to do this? Thank you so much!

 


Accepted Solutions
Solution
‎04-13-2016 11:08 PM
Super User
Posts: 9,662

Re: create a flag for first non-zero observation by group

data have;
input ID Value;
cards;
1       0
1       100
1       200
1       0
2       0
2       0
2       0
2       100
2       0
3       0
3       100
3       200
;
run;
data want;
 set have;
 by id;
 if first.id then n=0;
 if value ne 0 then n+1;
 flag=ifn(n=1 and value ne 0,1,0);
 drop n;
run;

View solution in original post


All Replies
Solution
‎04-13-2016 11:08 PM
Super User
Posts: 9,662

Re: create a flag for first non-zero observation by group

data have;
input ID Value;
cards;
1       0
1       100
1       200
1       0
2       0
2       0
2       0
2       100
2       0
3       0
3       100
3       200
;
run;
data want;
 set have;
 by id;
 if first.id then n=0;
 if value ne 0 then n+1;
 flag=ifn(n=1 and value ne 0,1,0);
 drop n;
run;
New Contributor
Posts: 4

Re: create a flag for first non-zero observation by group

Thank you! never used ifn function before!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 388 views
  • 0 likes
  • 2 in conversation