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;
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;
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;
This didn't give the correct output. 99999 is repeated through the A rows...
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;
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.
Ready to level-up your skills? Choose your own adventure.