topic Re: Deleting row with condition hash function in SAS Programming

Deleting row with condition hash function

bknitch — Fri, 13 Mar 2020 17:56:57 GMT

data have;
input MEMB_ID DUPFLAG MEMBER_LOB $;
datalines;
1233 0 JR
1234 1 WR
1234 1 WD 
1235 0 JD
1236 1 UR
1236 1 UD
1237 1 JD
1237 1 JR
1238 0 WD
1239 0 WR
;

data _null_;
set HAVE;
if _n_=1 then do;
 declare hash h(dataset: 'HAVE', multidata: 'y',ordered:'y');
  h.definekey('MEMB_ID');
   h.definedata('MEMB_ID','DUPFLAG','MEMBER_LOB');
  h.definedone();
  end;
set HAVE(where=(DUPFLAG=1 and MEMBER_LOB in ('JR','UR','WR'))) end=last;
if h.check()=0 then h.remove();
if last then h.output(dataset:'TEST1');
run;

I want to delete records in a large data set that contain multiple Member_LOB's. I've built a dup flag to identify this population, i want to remove any memb_id where a dupflag=1 and LOB ends in R but retain the members other LOB.

For example, ID 1234 i want to remove the record where LOB=WR but keep record where LOB=WD. I know my dupflag is causing my function to delete the records i want to retain. any help would be appreciated

These are my results

MEMB_ID	DUPFLAG	MEMBER_LOB
1233	0	JR
1235	0	JD
1238	0	WD
1232	0	JR
1239	0	WR

What i want is this.

MEMB_ID	DUPFLAG	MEMBER_LOB
1233	0	JR
1234	1	WD
1235	0	JD
1236	1	UD
1237	1	JD
1238	0	WD
1239	0	WR

Re: Deleting row with condition hash function

novinosrin — Fri, 13 Mar 2020 17:46:42 GMT

Hi @bknitch The description " i want to remove any memb_id where a dupflag=1 and LOB ends in R." is misleading to what's in your sample input/output. For example, why 1233 0 JR was dropped? Plus, I am not certain you need HASH approach albeit up-to you. Kindly review

Re: Deleting row with condition hash function

bknitch — Fri, 13 Mar 2020 17:49:17 GMT

I must have forgot to post it in my description, it was not removed. It retained it in my output.

Re: Deleting row with condition hash function

novinosrin — Fri, 13 Mar 2020 17:51:26 GMT

Okay no worries, Can you please edit the original post to the correct representation? This can help avoid any confusion

Re: Deleting row with condition hash function

bknitch — Fri, 13 Mar 2020 17:53:58 GMT

Just edited, my apologies.

Re: Deleting row with condition hash function

novinosrin — Fri, 13 Mar 2020 18:07:29 GMT

Thank you @bknitch Please see if this helps

data have;
input MEMB_ID DUPFLAG MEMBER_LOB $;
datalines;
1233 0 JR
1234 1 WR
1234 1 WD 
1235 0 JD
1236 1 UR
1236 1 UD
1237 1 JD
1237 1 JR
1238 0 WD
1239 0 WR
;
/*_n_=char(upcase(MEMBER_LOB),length(MEMBER_LOB))='R'; This checks if the END letter */
/*of MEMBER_LOB is "R"*/
data want;
 do until(last.MEMB_ID);
  set have;
  by memb_id;
  _n_=char(upcase(MEMBER_LOB),length(MEMBER_LOB))='R';
  if _n_ and DUPFLAG then continue;
  output;
 end;
run;

MEMB_ID	DUPFLAG	MEMBER_LOB
1233	0	JR
1234	1	WD
1235	0	JD
1236	1	UD
1237	1	JD
1238	0	WD
1239	0	WR

Re: Deleting row with condition hash function

bknitch — Fri, 13 Mar 2020 18:10:29 GMT

Awesome! That worked perfectly, thank you very much!