I am trying to create table which is in sas to postgressql but I have problem understanding what this sas code does. Here is my code:-

DATA  work."WC7I7AHE"n
         (keep = "DMMCU"n "DMITM"n "DMVEND"n "IBVEND"n "IBVEND_1110"n )
      ; 
   
   attrib "DMMCU"n length=$12 format=$12. label="Business Unit"
          "DMITM"n length=8 format=8. label="Item Number - Short"
          "DMVEND"n length=8 format=8. label="Supplier Number"
          "IBVEND"n length=8 format=8. label="Supplier Number"
          "IBVEND_1110"n length=8 format=8. label="Supplier Number"
          "IBITM"n length= 8
          "IBMCU"n length= $12
          ETLS_W2NX3KTY4 length= 8
          ; 
   
   retain missing0-missing1 0; 
   
   etls_assign_target_value = 0; 
   /* Build hash objects from lookup tables before reading first source row  */ 
   if (_n_ = 1) then 
   do; 
      /* Build hash h0 from lookup table etls_temp_lookupview0 */ 
      nlobs = .; 
      dsid = open("etls_temp_lookupview0"); 
      if (dsid > 0) then 
      do; 
         if ( attrc(dsid, 'MTYPE') = 'DATA' ) then 
            nlobs = attrn(dsid, 'NLOBS'); 
         else 
            nlobs = -1; 
            
         dsid = close(dsid); 
         if (nlobs ^= 0) then 
         do; 
            if (nlobs > 0) then 
               exponent = ceil(log2(nlobs)); 
            else 
               exponent = 8; 
            declare hash h0(dataset: "etls_temp_lookupview0", hashexp: exponent);
            h0.defineKey( "IBITM",
                           "IBMCU");
            h0.defineData( "IBVEND");
            h0.defineDone();
            if (nlobs = -1) then 
            do; 
               if (h0.Num_Items < 1) then
               do; 
                  put "NOTE: Lookup table is empty: etls_temp_lookupview0";
                  put "NOTE: Abort action indicated, condition= Lookup table is empty:"
                       " etls_temp_lookupview0";
                  
                  abort 3; 
               end; 
            end; 
         end; 
         else 
         do; 
            put "NOTE: Lookup table is empty: etls_temp_lookupview0";
            put "NOTE: Abort action indicated, condition= Lookup table is empty:"
                 " etls_temp_lookupview0";
            
            abort 3; 
         end; 
      end; 
      else 
      do; 
         put "NOTE: Lookup table does not exist or cannot be opened:"
              " etls_temp_lookupview0";
         
         put "NOTE: Abort action indicated, condition= Lookup table missing:"
              " etls_temp_lookupview0";
         
         abort 3; 
      end; 
      
      /* Build hash h1 from lookup table etls_temp_lookupview1 */ 
      nlobs = .; 
      dsid = open("etls_temp_lookupview1"); 
      if (dsid > 0) then 
      do; 
         if ( attrc(dsid, 'MTYPE') = 'DATA' ) then 
            nlobs = attrn(dsid, 'NLOBS'); 
         else 
            nlobs = -1; 
            
         dsid = close(dsid); 
         if (nlobs ^= 0) then 
         do; 
            if (nlobs > 0) then 
               exponent = ceil(log2(nlobs)); 
            else 
               exponent = 8; 
            declare hash h1(dataset: "etls_temp_lookupview1", hashexp: exponent);
            h1.defineKey( "IBITM");
            h1.defineData( "ETLS_W2NX3KTY4");
            h1.defineDone();
            if (nlobs = -1) then 
            do; 
               if (h1.Num_Items < 1) then
               do; 
                  put "NOTE: Lookup table is empty: etls_temp_lookupview1";
                  put "NOTE: Abort action indicated, condition= Lookup table is empty:"
                       " etls_temp_lookupview1";
                  
                  abort 3; 
               end; 
            end; 
         end; 
         else 
         do; 
            put "NOTE: Lookup table is empty: etls_temp_lookupview1";
            put "NOTE: Abort action indicated, condition= Lookup table is empty:"
                 " etls_temp_lookupview1";
            
            abort 3; 
         end; 
      end; 
      else 
      do; 
         put "NOTE: Lookup table does not exist or cannot be opened:"
              " etls_temp_lookupview1";
         
         put "NOTE: Abort action indicated, condition= Lookup table missing:"
              " etls_temp_lookupview1";
         
         abort 3; 
      end; 
      
      call missing ("IBITM"n, "IBMCU"n, "IBVEND"n, "ETLS_W2NX3KTY4"n); 
   
   end; /* All hash objects have been defined */
   
   /* Read a row from the source table  */ 
   set dmTBLS."DM_TURNOVER_TMP_VEND"n end = eof;
   
   /* Is the current key value stored in hash h0?  */ 
   rc0 = h0.find(key: "DMITM"n, 
   key: "DMMCU"n);
   /* Is the current key value stored in hash h1?  */ 
   rc1 = h1.find(key: "DMITM"n);
   "IBVEND_1110"n = ETLS_W2NX3KTY4;
   /* Examine success of lookups  */ 
   if ( rc0=0 and rc1=0 ) then 
   do; 
      /* Write row to target  */ 
      output work."WC7I7AHE"n; 


Till now I understood this part only

create table turnoverrate_tm.wc7i7ahe with these column dmmcu varchar(12), dmitm int, dmvend int, ibvend int, ibvend_1110 int

I couldn't understand what above sas code does.Please explain me.

I don't need code but just logic only

CodePudding user response：

From my point of view this code is clearly from SAS Data Integration Studio when you are using a lookup node, in this case with two lookups. So, in SAS DI Studio you could easily see what the intention of the code is, but to explain it in short form:

The main input table here is dmTBLS.DM_TURNOVER_TMP_VEND. Then there are two lookups, one concerning the table etls_temp_lookupview0, the other lookup table is etls_temp_lookupview1. For both lookups hashes are defined to lookup the values using the following keys. In the first case the keys are IBITM and IBMCU while the data we are looking for is IBVEND. In the second case the key is IBITM and the data column is ETLS_W2NX3KTY4 whose values are then put into the column IBVEND_1110. There are only some additional error routines that SAS creates with the lookup node. In the end, the output table work.WC7I7AHE is built up with the variables in the keep statement.