I have a data set that where I need to combnine several rows into one. See below
ID Date Var1 var2 var3
1 7/4/17 1 . .
1 7/4/17 . 0 .
1 7/4/17 . . 1
1 5/4/17 0 . .
1 5/4/17 . 0 .
1 5/4/17 . . 0
2 6/4/17 . 1 .
2 6/4/17 0 . .
2 6/4/17 . . 1
3 7/14/17 0 . .
3 7/14/17 . 0 .
3 7/14/17 . . 1
The desired outcome would be a single row per ID. Note - There are some ID that have data from an additional date. I need both.
ID Date Var1 Var2 Var3
1 7/4/17 1 0 1
1 5/4/17 0 0 0
2 6/4/17 0 1 1
3 7/14/17 0 0 1
Thank you for any help and input
One way:
proc sort data=have;
by id date;
run;
data want;
update have (obs=0) have;
by id date;
run;
One way:
proc sort data=have;
by id date;
run;
data want;
update have (obs=0) have;
by id date;
run;
proc sql;
select ID, Date, max(Var1) as var1, max(Var2) as Var2, max(Var3) as Var3
from have
group by ID, date;
quit;
data have;
input id date $ Var1 Var2 Var3;
cards;
1 7/4/17 1 . .
1 7/4/17 . 0 .
1 7/4/17 . . 1
1 5/4/17 0 . .
1 5/4/17 . 0 .
1 5/4/17 . . 0
2 6/4/17 . 1 .
2 6/4/17 0 . .
2 6/4/17 . . 1
3 7/14/17 0 . .
3 7/14/17 . 0 .
3 7/14/17 . . 1
;
run;
proc sort data = have;
by id date;
run;
data want;
set have;
by id date;
retain var1_ var2_ var3_;
if first.date then
do;
var1_ = .;
var2_ = .;
var3_ = .;
end;
if not missing (var1) then
var1_ = var1;
if not missing (var2) then
var2_ = var2;
if not missing (var3) then
var3_ = var3;
var1 = var1_;
var2 = var2_;
var3 = var3_;
if last.date then
output;
drop var1_ var2_ var3_;
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.