Use of RETAIN to grab informations in other observations

[jpprovost]

Hi folks,

I have a dataset that contains 3 types of observations, let says Payor (P), Insured (I) and Payor-Insured (PI).

I've made a simple dataset with the following code for demonstration purpose.

data test_retain;
infile datalines;
input
no_police 3.
rel_police $1.
no_cov 3.
rel_cov $1.
;
datalines;
110P
222P
    110I
    465I
;
run;

The first two lines are the payor of the policy #110 and #222. They are not insured, so they have missing values.

However, the insured of those two policies are the 3rd and 4th rows. They are not the payor, so they have missing values.

What I would like to do is to use the RETAIN function (I'm new with this one and I struggle a lot to do what I want to achieve) to scan the policy # (no_police) and to grab it back into the coverages line.

By example, the 3rd line is the insured of the 1st line which is the policy owner. Is there a way to use the RETAIN function to "scan" the policy number and when no_police = no_cov, then copy that no_police in the line of the insured ?

I've tried this but obvisouly I really don't know what I'm doing -.-.

data test_retain2;
set test_retain;
retain no_police;
if first.no_police = no_cov then no_cov = first.no_police;
run;

Any help would be very useful. Thanks in advance. The desired result should look like this:

110  P   .    .

222  P   .    .

110  .   110  I

.    .   465  I

[ballardw]

It will help to show an example of the output should look like for your given input. Especially given that you don't have a match for the NO_COV value in the 4th line of data.

I suspect that RETAIN is not what you want as that depends on the order of the data to be useful and I suspect that you cannot be sure of any given order.

Maybe something like:

proc sql;
   create table work.want as
   select a.no_police,a.rel_police, b.no_cov,b.rel_cov
   from (select * from work.test_retain where not missing (no_police) ) as a
        full outer join
        (select * from work.test_retain where not missing (no_cov) ) as b
        on a.no_police=b.no_cov
   ;
quit;

but if there are other variables involved you need to provide some example and the rules for which goes where.

And consider if you have multiple matching occurences of No_police and No_cov

(is No_police actually supposed to be No_Policy???)

[jpprovost]

It will help to show an example of the output should look like for your given input. Especially given that you don't have a match for the NO_COV value in the 4th line of data.

I edited the post while you were typing your answer, my bad.

For no_police it is in fact no_policy, my French took over my English 😉

[novinosrin]

data test_retain;
infile datalines;
input
no_police 3.
rel_police $1.
no_cov 3.
rel_cov $1.
;
datalines;
110P
222P
    110I
    465I
;
run;

data want;
 if _n_=1 then do;
 dcl hash H  () ;
 H.definekey ("no_police") ;
 H.definedata("no_police") ;
 H.definedone() ;
 do until(z);
  set test_retain(keep=no_police rel_police) end=z;
  if no_police and rel_police='P' then rc=h.add();
 end;
 end;
 z=0;
 set test_retain;
 if rel_cov='I' and missing(no_police) then 
 if h.check(key:no_cov)=0 then no_police=no_cov;
 drop rc;
 run;

[jdwaterman91 · Accepted solution]

Depending on what your ultimate goal is, it may be better to combine the data into a single rows where the no_police value equals the no_cov value (This also is dependent on whether you have rows with the same values in either of these columns).

However, to reach the output specified in your post, you can always create a macro variable to hold your no_police values and then check your no_cov variable against that. 

Proc SQL Noprint;
Select Distinct No_Police Into :Check Separated By ','
From Test_Retain
Where No_Police > 0;
Quit;

Data Want;
Set Test_Retain;

If No_Cov In (&Check.) Then No_Police = No_Cov;

Run;

[jpprovost]

Both solutions provided by @jdwaterman91 and @novinosrin works. A big thank to you!

However, let says that I have 5-6 columns linked to a policy number that I want to add to the left of the coverage line, is there a way to do it? Of course, those columns exists in the dataset, but for the insured part (coverage), thoses data are set to null...