topic Re: Looking up values in another table. in SAS Procedures

Looking up values in another table.

deleted_user — Fri, 27 Feb 2009 11:15:03 GMT

In Table1 there are many individuals. The individuals can can have values 1 - 10 for the variable DepCode.

In Table2 there are only the variables DepCode and Department. The variable Department contains the 10 DepCode values translated to text.

I want to look up the DepCode in Table2 and have the Department text transferred to a new variable, why not Department, in Table1.

I thought I could use LOCATEC or WHERE, but I get the error message: "The function X????? is unknown, or cannot be accessed.".

How could I solve this simple programming problem?

Susan

Re: Looking up values in another table.

Flip — Fri, 27 Feb 2009 13:02:42 GMT

data newdata;
set table1;
set table2 key = depcode;
if (not _iorc_ = %sysrc(_sok)) then do;
Department = ' ' ;
errtxt = " ";
_error_ = 0;
end;
run; Sorry, no parentheses needed there (Code off the top of my head may sometimes contain syntax errors).

This should give you the starting point to solve your problem.

Message was edited by: Flip

Re: Looking up values in another table.

deleted_user — Fri, 27 Feb 2009 13:29:40 GMT

Thanks!

But

set table2 (key = depcode);

gives ERROR 22-7: Invalid option name KEY.

Susan

Re: Looking up values in another table.

sbb — Fri, 27 Feb 2009 13:35:36 GMT

The SET statement SAS Data step DOC provides the answer to your error.

Scott Barry
SBBWorks, Inc.

Re: Looking up values in another table.

deleted_user — Mon, 02 Mar 2009 12:20:40 GMT

If I write the code like this:

data newdata;
set table1;
set table2 key = depcode;

I get the error message: No key variables have been defined for file table2

If I write like this:

data newdata;
set table1;
set table2; key = depcode;

the code runs, but I get the Departments from table2 from the beginning, with no regard to if the DepCode in table2 corresponds to the DepCode in table1.

Re: Looking up values in another table.

Flip — Mon, 02 Mar 2009 12:40:55 GMT

As Scott pointed out, read the documentation, don't expect cut and paste code from a forum. Lookups using a Key are quite powerful.

The quick answer to your question is that you should index your lookup dataset on the key you are using.

Flip

Re: Looking up values in another table.

Patrick — Mon, 02 Mar 2009 12:54:40 GMT

Just a version using PROC SQL.

You could also use the data set "department" to create a format (example in SAS OnlineDoc for Proc Format) and then apply this format in the Individuals table on the field "DepCode".
If you need the department names for printing only this would avoid to create redundant data in a second table (using a format statement). If you still would want to create a new variable: DepartmentName=put(depcode,depfmt.);

HTH
Patrick

data department;
depcode=1; department='a';output;
depcode=2; department='b';output;
depcode=3; department='c';output;
run;

data individuals;
IndCode=1;DepCode=1;output;
IndCode=2;DepCode=3;output;
IndCode=3;DepCode=2;output;
IndCode=4;DepCode=5;output;
run;

proc sql noprint;
create table individuals2 as
select ind.*,dep.department
from individuals ind left join department dep
on ind.depcode=dep.depcode;
quit;

proc datasets lib=work nodetails nolist;
delete individuals;
change individuals2=individuals;
quit;

proc print data=individuals;
run;

Re: Looking up values in another table.

deleted_user — Mon, 02 Mar 2009 13:22:41 GMT

"Read the documentation", could be the answer to all questions probably, in one way or the other.

But I have been searching the documentation för several hours without any result.

I didn't cut and paste your code. I wrote it by myself.

"Lookups using a Key are quite powerful." I can imagine that if I knew how to use it.

Susan

Re: Looking up values in another table.

Flip — Mon, 02 Mar 2009 13:43:09 GMT

GOOGLE "sas set key"

www2.sas.com/proceedings/sugi25/25/po/25p234.pdf
ftp.sas.com/samples/A60409

www.jmu.edu/docs/sasdoc/sashtml/accdb/z0612367.htm

Re: Looking up values in another table.

sbb — Mon, 02 Mar 2009 16:38:38 GMT

Suggest you reply to the group with SASLOG output revealing your SAS DATA and PROC step execution adjacent to the failing DATA step, also showing the error message you receive. The critical contributing factor here is the location of your KEY= parameter on the SET statement - something the DOC can help convey.

Scott Barry
SBBWorks, Inc.

Re: Looking up values in another table.

Flip — Mon, 02 Mar 2009 17:32:27 GMT

An executable example.
NOTE: you need to have an index on the lookup set, and you need to handle errors where there is no match. For small cases the format that Patrick refered to is great, but when you get into very large lookups this method is better.

data table2(index = (depcode));
depcode=1; department='a';output;
depcode=2; department='b';output;
depcode=3; department='c';output;
run;

data table1;
IndCode=1;DepCode=1;output;
IndCode=2;DepCode=3;output;
IndCode=3;DepCode=2;output;
IndCode=4;DepCode=5;output;
run;

data newdata;
set table1;
set table2 key = depcode;
if (not _iorc_ = %sysrc(_sok)) then do;
Department = ' ' ;
_error_ = 0;
end;
run;

Re: Looking up values in another table.

deleted_user — Tue, 03 Mar 2009 10:06:15 GMT

Thankyou all, for your commitment.

It worked to follow the above executable example.

Susan