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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: