Re-created SAS table into postgres sql

Question

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

I'm afraid in the current form, this question might go against SO recommendations/rules. If you try to pick out the bits that cause the most trouble and rephrase the question to target them, it would be accepted. I assume you do understand concepts of conditional expressions if...then...else... - cut those out. if...then do...end loops/code blocks - that as well. Whenever you see parentheses, check the documentation to see what the function does. — Zegarek
– Zegarek, Commented Nov 18, 2022 at 8:59
This looks like code from the SAS Data Integration Studio when using a Lookup Node. Do you use the SAS Data Integration Studio? — gregor
– gregor, Commented Nov 18, 2022 at 10:44
Are you asking how to define the structure of the table in your database language? Or are you asking how to recreate the logic to populate the table with observations (rows) of data? — Tom
– Tom, Commented Nov 18, 2022 at 15:20

gregor · Accepted Answer · 2022-11-18 11:09:12Z

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.

Collectives™ on Stack Overflow

Re-created SAS table into postgres sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related