topic Proc SQL to Hash table in SAS Procedures

Proc SQL to Hash table

Hima — Fri, 02 Mar 2012 00:07:24 GMT

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;

Proc SQL to Hash table

Haikuo — Fri, 02 Mar 2012 00:48:50 GMT

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

Proc SQL to Hash table

Hima — Fri, 02 Mar 2012 01:05:17 GMT

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.

Proc SQL to Hash table

Hima — Fri, 02 Mar 2012 02:12:58 GMT

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;

Proc SQL to Hash table

Haikuo — Fri, 02 Mar 2012 02:33:12 GMT

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;

Proc SQL to Hash table

Hima — Fri, 02 Mar 2012 03:00:23 GMT

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