Desktop productivity for business analysts and programmers

Joining tables using a value

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Joining tables using a value

I'm having troubles pulling rows from a dataset. They need to match a row in another dataset. Here's an example

DATA1

TYPE1TYPE2VALUE1VALUE2
HAIREYESBROWNBLUE
SEXEYESMALEBROWN
HAIRSEXBLONDEFEMALE
SEXEYESMALEBLUE

DATA2

NAMEHAIRSEXEYES
ROGERBROWNMALEBLUE
GLENBLONDEMALEHAZEL
JULIEBLONDEFEMALEBROWN

I want it to pull records in data2 based on matching the criteria in data1. The value in data1 will exactly match the column name in data2.

So the result would be:

ROGER

JULIE

Because their attributes match those laid out in data1.

I've spent a lot of time trying different approaches. Hopefully you guys can help.

Thanks!


Accepted Solutions
Solution
‎03-19-2013 11:12 PM
Super User
Super User
Posts: 6,844

Re: Joining tables using a value

Use the first dataset to generate a WHERE statement.  I find it is easiest to do this with a datat step and the PUT statement.

data criteria ;

  length type1 $32 value1 $20 type2 $32 value2 $20 ;

  input TYPE1 TYPE2 VALUE1 VALUE2 ;

cards;

HAIR EYES BROWN BLUE

SEX EYES MALE BROWN

HAIR SEX BLONDE FEMALE

SEX EYES MALE BLUE

run;

data records ;

  length NAME HAIR SEX  EYES $20 ;

  input name hair sex eyes ;

cards;

ROGER BROWN MALE BLUE

GLEN BLONDE MALE HAZEL

JULIE BLONDE FEMALE BROWN

run;

filename code temp;

data _null_;

  file code;

  if eof then put ';' ;

  set criteria end=eof;

  if _n_ = 1 then put 'WHERE';

  else put ' OR' ;

  put '(' type1 '= ' value1 :$quote. 'and ' type2 '= ' value2 :$quote. ')';

run;

data want ;

  set records ;

%inc code / source2 ;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,814

Re: Joining tables using a value

A bit tricky indeed...


data types;
array t{3} $ HAIR SEX EYES;
set data1;
t(findw("HAIR SEX EYES", trim(TYPE1), " ", "E")) = VALUE1;
t(findw("HAIR SEX EYES", trim(TYPE2) ," ", "E")) = VALUE2;
run;

proc sql;
select distinct d.name
from data2 as d, types as t
where sum(d.HAIR=t.HAIR, d.SEX=t.SEX, d.EYES=t.EYES) = 2;
quit;

PG

PG
Solution
‎03-19-2013 11:12 PM
Super User
Super User
Posts: 6,844

Re: Joining tables using a value

Use the first dataset to generate a WHERE statement.  I find it is easiest to do this with a datat step and the PUT statement.

data criteria ;

  length type1 $32 value1 $20 type2 $32 value2 $20 ;

  input TYPE1 TYPE2 VALUE1 VALUE2 ;

cards;

HAIR EYES BROWN BLUE

SEX EYES MALE BROWN

HAIR SEX BLONDE FEMALE

SEX EYES MALE BLUE

run;

data records ;

  length NAME HAIR SEX  EYES $20 ;

  input name hair sex eyes ;

cards;

ROGER BROWN MALE BLUE

GLEN BLONDE MALE HAZEL

JULIE BLONDE FEMALE BROWN

run;

filename code temp;

data _null_;

  file code;

  if eof then put ';' ;

  set criteria end=eof;

  if _n_ = 1 then put 'WHERE';

  else put ' OR' ;

  put '(' type1 '= ' value1 :$quote. 'and ' type2 '= ' value2 :$quote. ')';

run;

data want ;

  set records ;

%inc code / source2 ;

run;

New Contributor
Posts: 4

Re: Joining tables using a value

Perfect. Scalable for additional data and variables. Exactly what I was looking for. Thanks Tom.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 192 views
  • 3 likes
  • 3 in conversation