Solved
New Contributor
Posts: 4

# How to compress a data vertically using retain statement?

I'm trying to combine a data set with duplicate IDs but different outcomes for each variable into a single ID. All I want to know is the presence of the variable in any of the IDs. Below is an example. What would be the best way to perform this. My previous trials resulted in an incomplete datasets. Thanks

Accepted Solutions
Solution
‎12-18-2014 12:32 PM
Occasional Contributor
Posts: 10

## Re: How to compress a data vertically using retain statement?

Would this work for you?

data temp;
input tid 1.
tCar1 1.
tCar2 1.
tCar3 1.
tCar4 1.;
datalines;
11100
1011
1100
21100
;
run;

data temp1(drop=tid tCar1 tCar2 tCar3 tCar4);
set temp;
by tid;
retain id Car1 Car2 Car3 Car4;
if first.tid then do;
id = tid;
Car1 = tCar1;
Car2 = tCar2;
Car3 = tCar3;
Car4 = tCar4;
end;
else do;
if Car1 = 0 and tCar1 = 1 then Car1 = 1;
if Car2 = 0 and tCar2 = 1 then Car2 = 1;
if Car3 = 0 and tCar3 = 1 then Car3 = 1;
if Car4 = 0 and tCar4 = 1 then Car4 = 1;
end;
if last.tid then output;
run;

All Replies
Solution
‎12-18-2014 12:32 PM
Occasional Contributor
Posts: 10

## Re: How to compress a data vertically using retain statement?

Would this work for you?

data temp;
input tid 1.
tCar1 1.
tCar2 1.
tCar3 1.
tCar4 1.;
datalines;
11100
1011
1100
21100
;
run;

data temp1(drop=tid tCar1 tCar2 tCar3 tCar4);
set temp;
by tid;
retain id Car1 Car2 Car3 Car4;
if first.tid then do;
id = tid;
Car1 = tCar1;
Car2 = tCar2;
Car3 = tCar3;
Car4 = tCar4;
end;
else do;
if Car1 = 0 and tCar1 = 1 then Car1 = 1;
if Car2 = 0 and tCar2 = 1 then Car2 = 1;
if Car3 = 0 and tCar3 = 1 then Car3 = 1;
if Car4 = 0 and tCar4 = 1 then Car4 = 1;
end;
if last.tid then output;
run;

Not applicable
Posts: 1

## Re: How to compress a data vertically using retain statement?

data input;

input id car1-car4;

cards;

1 1 1 0 0

1 0 1 1 .

1 1 0 0 .

2 1 1 0 0

;

run;

proc means data=input nway noprint;

class id;

var car1-car4;

output out=mymax max= / autoname;

run;

data output (drop = car1-car4);

merge input mymax (drop = _type_ _freq_);

by id;

if last.id then output;

run;

title 'Input';

proc print data=input; run;

title ;

title 'Output';

proc print data=output; run;

title ;

quit;

New Contributor
Posts: 4