topic Re: Compressing Lookup Table in SAS Programming

Compressing Lookup Table

A_SAS_Man — Tue, 18 May 2021 16:23:11 GMT

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?

Re: Compressing Lookup Table

ballardw — Tue, 18 May 2021 16:28:38 GMT

Proc summary data=have nway;

class code;

var flag: ;

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

run;

might do it.

Re: Compressing Lookup Table

PeterClemmensen — Tue, 18 May 2021 16:52:55 GMT

Try this

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

Re: Compressing Lookup Table

ballardw — Tue, 18 May 2021 16:56:19 GMT

@PeterClemmensen wrote:

Try this

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

Looks familiar... 🤔

Re: Compressing Lookup Table

PeterClemmensen — Tue, 18 May 2021 16:57:59 GMT

Shoot, didn't see that!

Now I have to come up with another way 😄

Re: Compressing Lookup Table

ballardw — Tue, 18 May 2021 17:20:02 GMT

@PeterClemmensen wrote:

Shoot, didn't see that!

Now I have to come up with another way 😄

Take it as a "great minds" thing.