topic Re: Set to missing after a specific value in SAS Data Management

Set to missing after a specific value

pamplemousse822 — Wed, 11 Mar 2020 23:54:10 GMT

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;

Re: Set to missing after a specific value

RichardDeVen — Thu, 12 Mar 2020 00:54:00 GMT

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;

Re: Set to missing after a specific value

pamplemousse822 — Thu, 12 Mar 2020 01:02:50 GMT

This worked! Thank you!

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

Re: Set to missing after a specific value

RichardDeVen — Thu, 12 Mar 2020 03:47:59 GMT

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.