Compressing Lookup Table

[A_SAS_Man]

I have a large lookup table that has duplicate values. I'm joining this table based on the "code" field to other tables and adding the flag values to the other tables. Obviously in it's current format it is not possible to do because it creates a one to many join. I would like to compress this table while retaining the information in the lookup table. Below is what I have and the data set I'm trying to create (want).

data have;
input code flag1 flag2 flag3 flag4;
datalines;
4350 0 0 1 0
4350 1 0 0 0
4456 1 0 0 0
2345 0 0 0 1
1234 0 1 0 0
1234 1 0 0 0
;
run;

data want;
input code flag1 flag2 flag3 flag4;
datalines;
4350 1 0 1 0
4456 1 0 0 0
2345 0 0 0 1
1234 1 1 0 0
;
run;

I know I could break this out and do the joins to the lookup table separately, but because of the size of the tables involved I'm trying to avoid that. Is there a way to do what I'm thinking?

[ballardw · Accepted solution]

Proc summary data=have nway;

   class code;

   var flag: ;

   output out=want (drop=_:) max=;

run;

might do it.

[PeterClemmensen]

Try this

proc summary data = have nway;
   class code;
   var flag:;
   output out = want(drop = _:) max =;
run;

[ballardw]

---

@PeterClemmensen wrote:

Try this

proc summary data = have nway;
   class code;
   var flag:;
   output out = want(drop = _:) max =;
run;

---

Looks familiar... 🤔

[PeterClemmensen]

Shoot, didn't see that!

Now I have to come up with another way 😄

[ballardw]

---

@PeterClemmensen wrote:

Shoot, didn't see that!

Now I have to come up with another way 😄

---

Take it as a "great minds" thing.