topic Re: Fill up columns based on conditions from other columns in SAS Programming

Fill up columns based on conditions from other columns

twix17 — Thu, 06 Oct 2022 02:31:29 GMT

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.

filling up and down based on conditions

twix17 — Wed, 05 Oct 2022 22:12:02 GMT

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

Re: Fill up columns based on conditions from other columns

Kurt_Bremser — Thu, 06 Oct 2022 04:50:49 GMT

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;

Re: filling up and down based on conditions

AMSAS — Thu, 06 Oct 2022 16:14:51 GMT

Hi @twix17

I'm making the following assumptions

If the first ID, status is "on" then stat_no=1 and denom=1, based on your "want" data.
The status column in your 3rd observation in your "want" dataset is incorrect, it should be blank based on your "have" dataset
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 ;

Re: Fill up columns based on conditions from other columns

andreas_lds — Fri, 07 Oct 2022 06:37:02 GMT

Please don't double post questions.

Re: Fill up columns based on conditions from other columns

twix17 — Fri, 07 Oct 2022 13:44:37 GMT

Thank you. It worked.

Re: Fill up columns based on conditions from other columns

twix17 — Fri, 07 Oct 2022 13:45:55 GMT

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.

Re: filling up and down based on conditions

twix17 — Fri, 07 Oct 2022 13:46:01 GMT

Thank you