How do I lookup values from a table with defintions

[JoeSt]

Is there a procedure or other easy way to perform the lookup below? Or do I have to transpose the definition table and do a join on each column? My dataset contains many variables where I need to perform the this operation, so if there is a convenient way of doing this dynamically that would help allot.

Dataset:

type	shape	count
1	1	57
4	1	127
9	2	35
9	2	87

Definition table:

format	id	name
type	1	blue
type	4	gray
type	9	red
shape	1	round
shape	2	circle

Want:

type	shape	count
blue	round	57
gray	round	127
red	circle	35
red	circle	87

[ballardw]

One way:

proc sql;
   create table want as
   select b.name as type,b.format as shape, a.count
   from dataset as a
        left join
        definition as b
        on a.type=b.id
   ;
run;

Proc SQL will reorder the data. If you require the data in a specific order then add a variable to the dataset with the order you want, include a statement after the ON clause of : order by a.ordervariablename.

If you don't include a the order variable in the Select variable list then you'll likely get a note about that. The a. and b. are aliases that temporarily reference the data sets for short hand.

[PeterClemmensen]

I would probably transpose the data and apply the lookup logic from there

data one;
input type shape count;
datalines;
1	1	57
4	1	127
9	2	35
9	2	87
;

data two;
input format $	id	name $;
datalines;
type	1	blue
type	4	gray
type	9	red
shape	1	round
shape	2	circle
;

proc sort data=two;
   by id;
run;

proc transpose data=two out=lookup(drop=_NAME_);
   by id;
   id format;
   var name;
run;

[FreelanceReinh]

Do you really need new character variables (like TYPE and SHAPE) in the final dataset? In many applications formatted numeric values would be sufficient. In this case you wouldn't even need a new dataset.

/* Create formats */

proc format cntlin=two(rename=(format=fmtname id=start name=label));
run;

/* Assign formats */

proc datasets lib=work nolist;
modify one;
format type  type.
       shape shape.;
quit;

(using draycut's dataset names).

If some of the variable names were not suitable as format names (e.g. ending in a number), a bit more work would be required.

The FORMAT statement in the PROC DATASETS step could be made more dynamic if needed.