BookmarkSubscribeRSS Feed
msharma1788
Calcite | Level 5

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!

2 REPLIES 2
japelin
Rhodochrosite | Level 12

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
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 421 views
  • 1 like
  • 3 in conversation