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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.