I want to retain all the variables. Please see the raw data and desired data below -
data temp;
input ID x y z;
cards;
1 25 . .
1 . 35 .
1 . . 40
2 30 . .
2 . 25 .
3 50 . .
;
run;
data temp2;
set temp;
by ID;
retain x1 0;
if first.ID then X1=x;
else X1=X1;
run;
I have done it for variable x but i am unable to do it for other variables.
A double DO UNTIL() can perform this operation:
data temp;
input ID x y z;
cards;
1 25 . .
1 . 35 .
1 . . 40
2 30 . .
2 . 25 .
3 50 . .
;
data want;
array _a{3} x y z;
array _b{3};
do until(last.ID);
set temp; by ID;
do i = 1 to dim(_a);
_b{i} = coalesce(_b{i}, _a{i});
end;
end;
do until(last.ID);
set temp; by ID;
do i = 1 to dim(_a);
_a{i} = _b{i};
end;
output;
end;
drop i _b:;
run;
proc print; run;
data temp; input ID x y z; cards; 1 25 . . 1 . 35 . 1 . . 40 2 30 . . 2 . 25 . 3 50 . . ; run; proc means data=temp noprint; by id; var x y z ; output out=temp1 sum=; run; data want; set temp1(drop=_type_); do i=1 to _freq_; output; end; drop i _freq_; 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.