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?
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.