BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
11 REPLIES 11
Flip
Fluorite | Level 6
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
deleted_user
Not applicable
Thanks!

But

set table2 (key = depcode);

gives ERROR 22-7: Invalid option name KEY.

Susan
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
The SET statement SAS Data step DOC provides the answer to your error.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
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.
Flip
Fluorite | Level 6
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
Patrick
Opal | Level 21
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;
deleted_user
Not applicable
"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
Flip
Fluorite | Level 6
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Flip
Fluorite | Level 6
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;
deleted_user
Not applicable
Thankyou all, for your commitment.

It worked to follow the above executable example.

Susan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 3407 views
  • 0 likes
  • 4 in conversation