Fill up columns based on conditions from other columns

[twix17]

Data Have

id	status
1	off
1	on
1
1
2	on
2	off
2
2
3	on
3	off
3	on
3	off
4	off
4	off
4	off
4	off

Data want.

id	status	stat_no	denom
1	off	0	1
1	on	1	1
1		1	0
1		1	0
2	on	1	1
2	off	1	0
2		1	0
2		1	0
3	on	1	1
3	off	1	0
3	on	1	0
3	off	1	0
4	off	0	1
4	off	0	1
4	off	0	1
4	off	0	1

Hello everyone,

I have a table have and i want to create other columns based on the conditions.

stat_no column
For Id 1 with status 'off' meaning event has not occurred and the Id is still in the population I want the stat_no as 0, and the next status 'on' indicate an event has occured and the stat_no as 1. So as soon as the status 'on', I want all of stat_no = 1 irrespective of the value of the status within the id group.

Same for id 2, the first status is 'on', I want the rest of the stat_no to be 1 regardless if the value of the subsequent status of that Id.

denom column
For Id 1 with status 'off' denom is 1 and if the next value is 'on' then denom = 1 at the point but i want denom to be 0 for the subsequent values with the ID, regardless of the next value of the status.

Thanks.

[Kurt_Bremser · Accepted solution]

Looks like this:

data want;
set have;
by id;
retain stat_no denom;
if first.id
then do;
  stat_no = 0;
  denom = 1;
end;
if status = "on" then stat_no = 1;
if lag(status) = "on" and not first.id then denom = 0;
run;

[twix17]

Thank you. It worked.

[twix17]

data have

id	status
1	off
1	on
1
1
2	on
2	off
2
2
3	on
3	off
3	on
3	off
4	off
4	off
4	off
4	off

Table Want 

id	status	stat_no	denom
1	off	0	1
1	on	1	1
1	on	1	0
1		1	0
2	on	1	1
2	off	1	0
2		1	0
2		1	0
3	on	1	1
3	off	1	0
3	on	1	0
3	off	1	0
4	off	0	1
4	off	0	1
4	off	0	1
4	off	0	1

Hello everyone,

I have a status column showing on and off and missing values to create my want table.

I need help creating creating stat_column if my status is 'off' then my stat_no is 0 only if it is the first status as seen in id 1, that would count as denom is 1. if the next status for the same id is 'on', regardless of the value of the status after, I want subsequent value of stat_no to be 1 for the same Id. Also i want the denom to be 1 until the stat is 'on' but 0 afterwards.

Thanks.

Sammy

[AMSAS]

Hi @twix17 

I'm making the following assumptions

1. If the first ID, status is "on" then stat_no=1 and denom=1, based on your "want" data.
2. The status column in your 3rd observation in your "want" dataset is incorrect, it should be blank based on your "have" dataset
3. Your "have" dataset is sorted by ID

The following code, works for the sample data you supplied, I can't be sure it is what you want as your request isn't clear to me (probably why no one else has replied already)

data have ;
	infile cards ;
	input id status $ ;
cards ;
1	off
1	on
1	. 
1	. 
2	on
2	off
2	. 
2	. 
3	on
3	off
3	on 
3	off
4	off
4	off
4	off
4	off
;
run ;

data want_check ;
	infile cards ;
	input id status $ stat_no denom ;
cards;
1	off	0	1
1	on	1	1
1	on	1	0
1	. 	1	0
2	on	1	1
2	off	1	0
2	. 	1	0
2	. 	1	0
3	on	1	1
3	off	1	0
3	on 	1	0
3	off	1	0
4	off	0	1
4	off	0	1
4	off	0	1
4	off	0	1
run ;

data want ;
	keep id status stat_no denom ;
	set have ;
	retain 
		stat_no    0 
		denom      0
		count      1 
		status_flg 0 ;
	by id ;
	/* First id value */
	if first.id then do ;
		status_flg=0 ;
		count=1 ;
		/* if status="off" set stat_no=0 and denom=1 */
		if status="off" then do ;
			stat_no=0 ;
			denom=1 ;
		end ;
		/* Making assumption based on your want data set */
		/* if status="on"  set stat_no=1 and denom=1 */
		else do ;
			stat_no=1 ;
			denom=1 ;
		end ;
			
	end ;
	/* Not first id value */
	else do ;
		count=count+1 ;
		/* If the second id value */
		if count=2 then do ;
			if status="on" then do ;	
				stat_no=1 ;
				denom=1 ;
			end ;
		end ;
	end ;
	/* all id values */
	if status_flg=1 then
		denom=0 ;
	if status="on" then
		status_flg=1 ;
run ;

[twix17]

Thank you

[andreas_lds]

Please don't double post questions.

[twix17]

Sorry I did not double post question. the first one was flagged as SPAM and i had to do another one. I guess it was unflagged.