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?
Proc summary data=have nway;
class code;
var flag: ;
output out=want (drop=_:) max=;
run;
might do it.
Proc summary data=have nway;
class code;
var flag: ;
output out=want (drop=_:) max=;
run;
might do it.
Try this
proc summary data = have nway;
class code;
var flag:;
output out = want(drop = _:) max =;
run;
@PeterClemmensen wrote:
Try this
proc summary data = have nway; class code; var flag:; output out = want(drop = _:) max =; run;
Looks familiar... 🤔
Shoot, didn't see that!
Now I have to come up with another way 😄
@PeterClemmensen wrote:
Shoot, didn't see that!
Now I have to come up with another way 😄
Take it as a "great minds" thing.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.