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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.