Dear,
In my data three subjects with 11 obs present. The subject 'a' has all visit obs. The subject 'b' and 'c' has a few missing visits. I need to populate all the visits with values from previous 'aval' variable values. Each subject should have all the visit obs
Eg;
Subject b has missing visit 2. The 'visitnum= 2' obs is populated with aval from visitnum;=1
Thank you
data one; input name $ visitnum aval; datalines; a 1 10 a 2 20 a 3 30 a 4 40 a 5 50 b 1 10 b 3 30 b 4 40 b 5 50 c 1 10 c 2 20 ; out put needed; a 1 10 a 2 20 a 3 30 a 4 40 a 5 50 b 1 10 b 2 10 b 3 30 b 4 40 b 5 50 c 1 10 c 2 20 c 3 20 c 4 20 c 5 20
data one;
input name $ visitnum aval;
datalines;
a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 10
b 3 30
b 4 40
b 5 50
c 1 10
c 2 20
;
data want;
set one;
by name;
_k=lag(visitnum);
_k1=lag(aval);
_visitnum=visitnum;
_aval=aval;
if not first.name and dif(visitnum)>1 then do;
visitnum=_k+1;
aval=_k1;
output;
visitnum=_visitnum;
aval=_aval;
output;
end;
else output;
drop _:;
run;
The following should work:
data one; input name $ visitnum aval; datalines; a 1 10 a 2 20 a 3 30 a 4 40 a 5 50 b 1 10 b 3 30 b 4 40 b 5 50 c 1 10 c 2 20 ; data want (keep=name visitnum aval); set one; by name; array avals(5); retain avals:; if first.name then call missing(of avals(*)); avals(visitnum)=aval; if last.name then do i=1 to 5; if missing(avals(i)) then avals(i)=avals(i-1); aval=avals(i); visitnum=i; output; end; run;
Art, CEO, AnalystFinder.com
data one; input name $ visitnum aval; datalines; a 1 10 a 2 20 a 3 30 a 4 40 a 5 50 b 1 10 b 3 30 b 4 40 b 5 50 c 1 10 c 2 20 ; proc sql; create table temp as select a.*,b.aval from ( select * from (select distinct name from one), (select distinct visitnum from one) ) as a left join one as b on a.name=b.name and a.visitnum=b.visitnum ; quit; data want; set temp; by name; retain new; if first.name then call missing(new); if not missing(aval) then new=aval; 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.