vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi,
There are 2 SAS tables/datasets,
XX.PRODUCTS
| Code: |
prodnum prodname manunum prodtype prodcost
1110 Dream Machine 111 Workstation $3,200.00
1200 Business Machine 120 Workstation $3,300.00
1700 Travel Laptop 170 Laptop $3,400.00
2101 Analog Cell Phone 210 Phone $35.00
2102 Digital Cell Phone 210 Phone $175.00
2200 Office Phone 220 Phone $130.00
5001 Spreadsheet Software 500 Software $299.00
5002 Database Software 500 Software $399.00
5003 Wordprocessor Software 500 Software $299.00
5004 Graphics Software 500 Software $299.00
|
XX.INVOICE
| Code: |
invnum manunum custnum invqty invprice prodnum
1001 500 201 5 $1,495.00 5001
1002 600 1301 2 $1,598.00 6001
1003 210 101 7 $245.00 2101
1004 111 501 3 $9,600.00 1110
1005 500 801 2 $798.00 5002
1006 500 901 4 $396.00 6000
1007 500 401 7 $23,100.00 1200
|
The aim is to fetch the prodcost from XX.PRODUCTS for all the products that are present in the XX.INVOICE table.
Output:
| Code: |
Produnum Prodcost
---------------------
1110 $3,200.00
1200 $3,300.00
2101 $35.00
5001 $299.00
5002 $399.00 |
I am aware that we can get this done using inner join or merge, but this is a learning exercise for hash objects in SAS.
So tried the basic hash template,
| Code: |
data merged;
if _n_ = 1 then do;
if 0 then set xx.products(keep = prodnum prodcost);
declare hash prodhash (dataset: "xx.products");
prodhash.definekey ("prodnum");
prodhash.definedata ("prodcost");
prodhash.definedone();
call missing(prodcost);
end;
set xx.invoice;
prodhash.find(key:prodnum);
keep prodnum prodcost;
output;
run; |
but it comes up with error,
| Code: |
NOTE: There were 10 observations read from the data set XX.PRODUCTS.
ERROR: Key not found.
ERROR: Key not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 7 observations read from the data set XX.INVOICE.
WARNING: The data set WORK.MERGED may be incomplete. When this step was
stopped there were 7 observations and 2 variables.
WARNING: Data set WORK.MERGED was not replaced because this step was
stopped. |
Could you please let me know whats going wrong here.
Thanks, |
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
After some searching on the internet found that
| Code: |
prodhash.find(key:prodnum);
has to be
rc = prodhash.find(key:prodnum);
|
And the reason for 2 "ERROR:Key not found" messages.
was for products 6000 and 6001 which were in invoice table, but not on products table. |
|
