BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hima
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

5 REPLIES 5
Haikuo
Onyx | Level 15

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

Hima
Obsidian | Level 7

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.

Hima
Obsidian | Level 7

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;

Haikuo
Onyx | Level 15

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;

Hima
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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