Help using Base SAS procedures

Proc SQL to Hash table

Accepted Solution Solved
Reply
Regular Contributor
Posts: 233
Accepted Solution

Proc SQL to Hash table

I have tried so hard to convert the proc sql into hash but was not getting expected results. Please help.

PROC SQL;

CREATE TABLE TMP2 AS

SELECT A.*, B.COL1, B.COL2, B.COL3, B.COL4, CASE WHEN (A.PRIM_KEY= B.PRIM_KEY AND B.COL1='INACT' AND B.COL2 IN ('INACTIVE 36+','NEVER 18+') ) 

THEN 'FAIL' ELSE 'PASS' END AS FLAG

FROM TMP1 A LEFT JOIN TABLE B ON A.PRIM_KEY= B.PRIM_KEY;

QUIT;


Accepted Solutions
Solution
‎03-01-2012 07:48 PM
Respected Advisor
Posts: 3,156

Proc SQL to Hash table

Obviously not tested.

data TMP2 (DROP=RC);

   if _n_=1 then do;

      if 0 then set table;

       declare hash hh (dataset: 'table');

       hh.definekey ('prim_key');

       hh.definedata('col1','col2','col3','col4');

       hh.definedone();

    end;

       set TMP1;

       rc=hh.find();

       if rc=0 then do;

           if col1='INTACT' AND COL2 IN ('INACTIVE 36+', 'NEVER 18+') THEN flag='FAIL'; else flag='PASS';

        end;

        output;

        CALL MISSING (OF _ALL_);

        run;

Let me know how it works.

Regards,

Haikuo

View solution in original post


All Replies
Solution
‎03-01-2012 07:48 PM
Respected Advisor
Posts: 3,156

Proc SQL to Hash table

Obviously not tested.

data TMP2 (DROP=RC);

   if _n_=1 then do;

      if 0 then set table;

       declare hash hh (dataset: 'table');

       hh.definekey ('prim_key');

       hh.definedata('col1','col2','col3','col4');

       hh.definedone();

    end;

       set TMP1;

       rc=hh.find();

       if rc=0 then do;

           if col1='INTACT' AND COL2 IN ('INACTIVE 36+', 'NEVER 18+') THEN flag='FAIL'; else flag='PASS';

        end;

        output;

        CALL MISSING (OF _ALL_);

        run;

Let me know how it works.

Regards,

Haikuo

Regular Contributor
Posts: 233

Proc SQL to Hash table

I am runing the code. So far no errors in the log. It has about 51 million records in it. Once done, I will let you know. Thanks again for helping.

Regular Contributor
Posts: 233

Proc SQL to Hash table

It worked. Thank you. I need help with one more. can you please help me?

RSUBMIT;
PROC SQL;
CREATE TABLE TMP6  AS
SELECT A.*, CASE WHEN (A.PRIM_KEY= H.PRIM_KEY) THEN 'FAIL' ELSE 'PASS' END AS CONVERSION_OPT_OUT
FROM TMP5 A LEFT JOIN   UPGRADEEXCLUDE H ON A.PRIM_KEY= H.PRIM_KEY;
QUIT;
ENDRSUBMIT;

Respected Advisor
Posts: 3,156

Proc SQL to Hash table

Again, not tested. Good Luck! Haikuo

data TMP6;

   if _n_=1 then do;

      if 0 then set UPGRADEEXCLUDE (keep=prim_key);

       declare hash hh (dataset: 'UPGRADEEXCLUDE');

       hh.definekey ('prim_key');

       hh.definedata('prim_key');

       hh.definedone();

    end;

       set TMP5;

       _n_=hh.find();

       if _n_=0 then CONVERSION_OPT_OUT='FAIL'; else CONVERSION_OPT_OUT='PASS';

        run;

Regular Contributor
Posts: 233

Proc SQL to Hash table

Thank you so much. You helped me so much...

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 226 views
  • 3 likes
  • 2 in conversation