topic Re: How do I lookup values from a table with defintions in SAS Data Management

How do I lookup values from a table with defintions

JoeSt — Thu, 28 Mar 2019 15:55:24 GMT

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

Re: How do I lookup values from a table with defintions

ballardw — Thu, 28 Mar 2019 16:21:27 GMT

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.

Re: How do I lookup values from a table with defintions

PeterClemmensen — Thu, 28 Mar 2019 16:38:02 GMT

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;

Re: How do I lookup values from a table with defintions

FreelanceReinh — Thu, 28 Mar 2019 17:05:54 GMT

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.