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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.