topic using CNTLIN or resolution for VLookup in SAS in New SAS User

using CNTLIN or resolution for VLookup in SAS

pdata — Wed, 04 May 2022 11:50:19 GMT

I am new to CNTLIN and tried to make use of it and failed.

I have the following table ID, SSN and COMP_SSN and I have to create then new column MULTI_FLAG

Where the combination of SSN & COMP_SSN is repeated, then the entire ID row has to be tagged as Y.

data DS_To_Flag;
input ID SSN $ COMP_SSN $;
datalines;
123 999 0
333 666 987
444 765 0
9996 987 0
876 8889 999
777 8889 0
;
run;

e.g: For the row where ID = 333 -> SSN of 666 is unique, while COMP_SSN of 987--- the same 987 does exist in SSN List...hence Multi_flag will be Y..

*Wanted Column -> MULTI_FLAG

ID SSN COMP_SSN MULTI_FLAG
123 999 0 Y
333 666 987 Y
444 765 0 N
9996 987 0 Y
876 8889 999 Y
777 8889 0 Y

I am trying to achieve this by CNTL...

IF not please suggest the resolution in creating the new MULTI_FLAG

* I need the new flag column as Multi_Flag

Re: using CNTLIN or resolution for VLookup in SAS

andreas_lds — Wed, 04 May 2022 06:09:41 GMT

Please provide data in usable form: a data step using datalines or proc sql with insert.

Re: using CNTLIN or resolution for VLookup in SAS

pdata — Wed, 04 May 2022 11:52:17 GMT

Thank you Andreas_Ids... my bad..
However, I have updated the post and added the Dataset block for the sample Data.

Re: using CNTLIN or resolution for VLookup in SAS

Tom — Wed, 04 May 2022 12:12:41 GMT

A CNTLIN dataset would just be a way to define a format. Please show what format you would define for given example and how you would use it.

Not sure I get the logic here.

Is the goal to flag any observation where either SSN that has more than one ID associated with it?

So perhaps just find the SSN that have multiple IDs associated and make for format that just has those values of SSN?

data DS_To_Flag;
input ID SSN $ COMP_SSN $;
datalines;
123 999 0
333 666 987
444 765 0
9996 987 0
876 8889 999
777 8889 0
;

proc transpose data=ds_to_flag out=ssn(rename=(col1=ssn) where=(ssn not in ('0',' '))) name=type ;
  by id notsorted;
  var ssn comp_ssn;
run;

proc print;
run;

proc sql ;
create table cntlin as
  select 'ssndup' as fmtname
       , 'C' as type
       , ssn as start
       , 'Y' as label
  from ssn
  group by 1,2,3,4
  having count(*) > 1
;
quit;

proc format cntlin=cntlin fmtlib;
run;

data want ;
  set ds_to_flag;
  if put(ssn,$ssndup.)='Y' or put(comp_ssn,$ssndup.)='Y' then multi_flag='Y';
  else multi_flag='N';
run;

proc print;
run;

Re: using CNTLIN or resolution for VLookup in SAS

pdata — Wed, 04 May 2022 12:45:10 GMT

Thank You Tom..

Yes, the resolution provided did work very well...you got the logic right.
ID is unique and however, the value of SSN having found multiples either in SSN or COMP_SSN...or COMP_SSN having found multiples either in SSN or COMP_SSN then the Multi_Flag is Y..

I never had success using CNTLIN and your steps did make me understand it and Data Transpose is which I never though off..

Tom, thank you so much... You made it very clear.