topic How Do I create a ID based on a prior observation? in SAS Programming

How Do I create a ID based on a prior observation?

southbaymax — Fri, 17 Aug 2018 00:01:04 GMT

If I had these values in excel

BR VALUE

1 7184
0 9759
1 9988

0 6780

0 6398

1 7107

0 5776

0 3719

and I used this formula in excel IF(A2=1,B2,B1)

I would get this.

BR VALUE ID

1 7184
0 9759 7184
1 9988 9988

0 6780 9988

0 6398 9988

1 7107 7107

0 5776 7107

0 3719 7107

How can I do the same thing in SAS?

Re: How Do I create a ID based on a prior observation?

Astounding — Fri, 17 Aug 2018 00:55:40 GMT

It's a little convoluted because of the need to keep the first ID blank. If I understand it correctly:

data want;

set have;

if BR=1 then latest_value = value;

retain latest_value;

if _n_ > 1 then value = latest_value;

drop latest_value;

run;

That should work for the data you posted. I'm just not 100% certain of the requirements for some of the possibilities such as what happens if BR=0 for the first observation.

Re: How Do I create a ID based on a prior observation?

Criptic — Fri, 17 Aug 2018 06:34:14 GMT

Just to elaborate a bit on the code posted by @Astounding and formatting it:

data work.table_1;
	input br value;
	datalines;
1        7184
0        9759
1        9988
0        6780
0        6398
1        7107
0        5776
0        3719
;
run;

* _id is a temp variable which stores the latest value where br is 1;
data work.table_2(drop=_id);
	length id 8.;
	set work.table_1;
	
	if br=1 then
		_id = value;

	* _N_ contains the current iteration number;
	if _N_ > 1 then
		id = _id;

	* retains the value from iteration to iteration;
	retain _id;
run;