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;
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
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
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.
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;
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;
Thank you so much. You helped me so much...
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.