Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SAS - simple merge with hash.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1521
Location: Chennai

PostPosted: Tue Mar 27, 2012 12:05 am    Post subject: SAS - simple merge with hash.
Reply with quote

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,
Back to top
View user's profile Send private message

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1521
Location: Chennai

PostPosted: Tue Mar 27, 2012 11:00 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts SQL Merge error -254 Robin Sulsona DB2 8 Wed Jun 01, 2016 8:28 pm
No new posts SAS MERGE to DFSORT Musab Ahmed DFSORT/ICETOOL 6 Mon Feb 01, 2016 5:44 pm
No new posts Merge two files having two trailer re... R.Manivannan DFSORT/ICETOOL 7 Wed Nov 25, 2015 9:18 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us