Set to missing after a specific value

[pamplemousse822]

Hi, 

I want to create a dataset that looks like data "want" below, for id 1-100. This is for an exercise so the values don't matter, but the data structure needs to follow these rules: 

• 6 rows (time 0-5) for every id.
• For each id, once y=1, then next rows will be missing. 
• For each id, y=0 for rows before y=1. 
• 20% of the ids have y=1. 

I have tried a few approaches, but I am running into issues. For example, this attempt goes from wide to long. Within each id, how do I set values after 1 to missing? 

Thanks in advance!

data sim1; 
	do id=1 to 100;
	year0=0; 
	year1=1;
	year2=2;
	year3=3;
	year4=4;
	year5=5;
	output; end;
run;

proc transpose data=sim1 out=sim2; 
	by id;  
run;

data sim3; 
	set sim2 (rename=(COL1=year)); 
		y=rand("BINOMIAL", 0.2, 1);
	drop _name_;
run; 

data want; 
	input id y time; 
	datalines; 
		1 1 0
		1 . 1
		1 . 2
		1 . 3
		1 . 4
		1 . 5
		2 0 0
		2 0 1
		2 0 2
		2 1 3
		2 . 4
		2 . 5
		;
run;

[RichardDeVen · Accepted solution]

In a DATA Step use a retained flag variable to track the occurrence of y=1 in the ID group.

Set the y value to missing when the flag is active.

In this example, because y is 0 or 1, the flag can be assigned to the y value (up to the point at which y=1).  After flag is assigned 1 the else never occurs again (within the BY group.)

data want;
  set sim3; 
  by id;

  if first.id then flag = 0;

  if flag then 
    y  = .;
  else
    flag = y;

  retain flag; drop flag;
run;

[pamplemousse822]

This worked! Thank you!

Can you explain how the flag knows to stop at 1?

[RichardDeVen]

Essentially, once flag is set if flag then y=.; clause will always happen -- flag will never change value until the next first.id=1 occurs

It's a matter of figuring out a 'state machine' that produces the values relevant to your processing needs.

• At first.ID the flag is reset to 0
• Then, because the flag is 0 the else clause is executed, and the flag is set to the y value( flag=y).
  • If y=0 the flag remains 0
  • if y=1 the flag is set to 1
• Control returns to the top of the step and the next row is read into the PDV

Let's unroll that return to top.

• flag is retained, so it's value will persist across rows, and will only change when it is assigned a new value
• At second observation (and subsequent obs), presuming we are in same ID group:
  • the flag is NOT reset in the if first.id because by processing will cause first.ID to be 0
  • if the prior row had y=0, flag will be 0 and the else clause is executed again and the flag is set to the current y value
  • if the prior row had y=1, flag will be 1 and the then clause is executed, setting y to missing.
    • Because flag is 1 if flag will always be true, and thus
    • flag will not change until the next first.id, so all future rows of group will have flag = 1 resulting in a y=. assignment.
  •