topic How to retain a value until conditions met in SAS Programming

How to retain a value until conditions met

SASAlex101 — Tue, 07 Feb 2023 05:19:43 GMT

I have this table;


data WORK.input;
length ID $1. Type $10.;
infile datalines delimiter='#';
input ID $ Type $ Val sortorder;
datalines;
1#A#100#1
1#A#100#2
1#A#100#3
1#A#100#4
1#A#100#5
1#A#100#6
1#X#999999#7
1#X#999999#8
1#X#999999#9
1#X#999999#10
1#A#700#11
1#A#700#12
1#A#700#13
1#A#700#14
2#A#300#1
2#A#300#2
2#A#300#3
2#A#300#4
2#X#999999#5
2#X#999999#6
2#A#500#7
2#A#500#8
2#X#999999#9
2#A#800#10
;
run;

I've included a sort order column. What I'm trying to do is when we hit the first row where Type = X I want to retain the last known Val for each row thereafter until we hit type <> X then we will set the new Val.

I have tried this code, but it appears to be working (mostly) except for lines 1, 11,21, and 24 are showing NULL or the Val on the X row.

data work.input2(DROP=sortorder);
set work.input;
LagVal = LAG(Val);
if first.ID then LagVal = .;
retain LastKnownValBeforeX;
if Type ne 'X' then LastKnownValBeforeX = LagVal;
run;

the picture below explains my issue and desired output:

Thanks in advance;

Re: How to retain a value until conditions met

Kurt_Bremser — Tue, 07 Feb 2023 06:41:54 GMT

You need a RETAINed variable:

data want;
set input;
by id;
retain newval;
if first.id then newval = .;
if type = "X" then newval = val;
else if newval ne . then val = newval;
drop newval;
run;

Re: How to retain a value until conditions met

SASAlex101 — Tue, 07 Feb 2023 14:46:27 GMT

This didn't give the correct output. 99999 is repeated through the A rows...

Re: How to retain a value until conditions met

SASAlex101 — Tue, 07 Feb 2023 14:53:01 GMT

GOT IT!

data output;
set input;
by id sortorder Type;
retain newvar;
if Type='A' then newvar= val;
if Type ne 'X' then newvar = newvar;
output;
run;

Re: How to retain a value until conditions met

SASAlex101 — Tue, 07 Feb 2023 14:53:35 GMT

Props to Kurt for helping find this solution!