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...
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.
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.