topic Re: want to knock off the records in SAS Programming

want to knock off the records

rohithverma — Mon, 08 Jun 2020 02:13:25 GMT

I have a data like below :

Loanno dramt refno creditamt

. . 1234 50

. . 1234 100

1234 100 . .

I need output like below

dataset1:-

Loanno dramt refno creditamt

. . 1234 100

1234 100 . .

dataset2:-

Loanno dramt refno creditamt

. . 1234 50

Please help me . thanks in advance

Re: want to knock off the records

Reeza — Mon, 08 Jun 2020 02:41:45 GMT

What are the rules there? Why does the missing get split between the data sets?

If it's positional - ie last record in one data set and others in second dataset that's trivial with first/last.

data part1 part2;
set have;
by loanno dramt;
if last.loanno then output part2;
else output part1;
run;

Re: want to knock off the records

rohithverma — Mon, 08 Jun 2020 02:45:35 GMT

Sorry it won't be positional .Dynamically the records order will change .

Due to one to one merging i have got those dots mean missing values

Re: want to knock off the records

Reeza — Mon, 08 Jun 2020 02:47:42 GMT

So what is the logic. Why do some dots go to one data set and others to the other dataset?

You should fix your join, not fix it after the fact. This means that you have records in one data set that don't match in the other OR that you have missing in both and it's joining on the missing. Check your data before the merge and after to ensure you're getting the correct results.

Re: want to knock off the records

rohithverma — Mon, 08 Jun 2020 03:07:25 GMT

i have a dataset like below intially

dataset1

Loanno dramt cramt

1234 100 0

dataset2

refno creditamount

1234 50

1234 100

The merging should be done based on Loanno and if dramt = creditamount then flag should come as "Matched" and i need the original data as it is in the intial stage . So i have used the code like below.

proc sort data =data1;by loanno dramt ;run;

proc sort data=data2 ;by loanno creditamount;run;

data first;

set data1;

by loanno ;

if first.loanno =1 then d=1;else d+1;run;

data second;

set data2;

by refno;

if first.refno=1 then d=1; else d+1;run;

data want;

merge data1 (in=a) data2(in=b);

by loanno amount d;

run;

I have named loanno1 as common in both the datasets and i have merged .As i need the structure of original data after merging also .So to avoid overlapping of records i have generated a distinct value using D and i have merged

Re: want to knock off the records

Patrick — Mon, 08 Jun 2020 04:05:06 GMT

What you show us in your latest post and what you've posted initially doesn't align - plus the code you've posted now throws errors if executed.

Going back to your initial question below code returns what you've asked for. If that's also going to work with your real data will depend on how representative the sample is you've provided (like: I'm missing a datetime column in your sample data).

data have;
  input Loanno dramt refno creditamt;
  datalines;
 . . 1234 50
 . . 1234 50
 . . 1234 100
1234 100 . . 
;

data want1 want2;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'have(where=(not missing(loanno)))', multidata:'y');
      h1.defineKey('loanno', 'dramt');
      h1.defineData('Loanno', 'dramt');
      h1.defineDone();
    end;

  set have;
  if h1.check(key:refno,key:creditamt)=0 then
    do;
      output want1;
      h1.removedup(key:refno,key:creditamt);
    end;
  else if not missing(loanno) then output want1;
  else output want2;
run;

proc print data=want1;
run;
proc print data=want2;
run;