How do I create Bypass report from Final dataset.

[msharma1788]

Hi Team,

I have below data in my final dataset.

SSN REC_TYPE Name
123 53 Mukesh
123 35 Mukesh
123 01 Mukesh
234 53 Rajesh
234 35 Rajesh
345 53 Madhan
345 35 Madhan
345 01 Madhan
456 35 Richa

I want to create a bypass report if my SSN is not present in any of the REC_TYPE (35,53,01)

I am expecting below result for my Bypass dataset.

234 53 Rajesh
234 35 Rajesh

456 35 Richa

Thanks!

[japelin]

How is tihs code?

data have;
  length  ssn 8 rec_type $2 name $8;
  input ssn rec_type name;
datalines;
123 53 Mukesh
123 35 Mukesh
123 01 Mukesh
234 53 Rajesh
234 35 Rajesh
345 53 Madhan
345 35 Madhan
345 01 Madhan
456 35 Richa
;
run;

/* transpose */
proc transpose data=have out=trans prefix=var_;
  var name;
  by ssn;
  id rec_type;
run;

/* Return transpose where SSN is not present in any of the REC_TYPE (35,53,01)  */
proc transpose data=trans(where=(var_01='' or var_35='' or var_53='')) out=next;
  var var:;
  by ssn;
run;

/* rename and modify data */
data want(drop=_name_);
  length  ssn 8 rec_type $2 name $8;
  set next;
  rec_type=scan(_NAME_,2,'_');
  where name ne '';
run;

[Ksharp]

data have;
input SSN REC_TYPE Name $;
cards;
123 53 Mukesh
123 35 Mukesh
123 01 Mukesh
234 53 Rajesh
234 35 Rajesh
345 53 Madhan
345 35 Madhan
345 01 Madhan
456 35 Richa
;

proc sql;
create table want as
select * from have where ssn not in (
select distinct ssn from have 
 group by ssn
  having sum(REC_TYPE=35)>0 and sum(REC_TYPE=53)>0 and sum(REC_TYPE=01)>0 
);
quit;