☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## How to retain a value until conditions met

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:

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: How to retain a value until conditions met

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;

4 REPLIES 4
Super User

## Re: How to retain a value until conditions met

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;``````
Quartz | Level 8

## Re: How to retain a value until conditions met

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

Quartz | Level 8

## Re: How to retain a value until conditions met

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;

Quartz | Level 8

## Re: How to retain a value until conditions met

Props to Kurt for helping find this solution!
Discussion stats
• 4 replies
• 1212 views
• 0 likes
• 2 in conversation