BookmarkSubscribeRSS Feed
sas_Forum
Calcite | Level 5

I am having Mob no
if length =10 and first digit ne 0 then i should not consider that at that point the data
should be ''
if length =11 and first digit=0 then i should remove the 0 and take the rest in to variable
if length =12 and first two digit=91 then i should remove the 91 and take the rest in to variable


data t1;
infile cards missover truncover;
input name $ Mob_No $ 3-20;
org=mob_no;
cards;
a 91+1234506789
a 98-12345678
a 01234506789
a 1234506789
a M-1234506789
a b-1234506789
a 911234506789
a 911234506789
a 01234506789
a 8989
a 98123456789
a 0123456789
a 1234056789
;
run;


data test;
set t1;
Mob_No=upcase(Mob_No);
Mob_No=trim(compress(Mob_No,' +-_()ABCDEFGHIJKLMNOPQRSTUVWXYZ'));
mob_len=length(Mob_No);
if mob_len in(10,11,12) then Mob_No=Mob_No;
else Mob_No='';

if mob_len=10 and substr(Mob_No,1,1) ne '0' then Mob_No=Mob_No;
else if  mob_len=11 and substr(Mob_No,1,1)='0' and substr(Mob_No,2,1) ne '0'   then
Mob_No=substr(Mob_No,2,12);
else  mob_no=mob_no;
if mob_len=12 and substr(Mob_No,1,2)='91' and substr(Mob_No,3,1) ne'0'
then Mob_No=substr(Mob_No,3,12);
else mob_no=mob_no;
/*/*Mob_No1=Mob_No;*/*/
/*/*Mob_len2=length(Mob_No1);*/*/
/*/*if mob_len2=11 and substr(Mob_No1,1,1) ='0' then Mob_No1=substr(Mob_No1,2,12);*/*/
/*/*else if mob_len2=12 and substr(Mob_No1,1,2)='91' then Mob_No1=substr(Mob_No1,3,12);*/*/
/*/*else Mob_No1=Mob_No1;*/*/

run;
proc print;
run;


output should be


a 1234506789
a 9812345678
a 1234506789
a 1234506789
a 1234506789
a 1234506789
a 1234506789
a 1234506789
a 1234506789
a
a
a
a 1234056789

1 REPLY 1
Haikuo
Onyx | Level 15

By enforcing your current rules, this is the closest I can get:

data t1;

infile cards missover truncover;

input name $ Mob_No $ 3-20;

org=compress(mob_no,,'kd');

cards;

a 91+1234506789

a 98-12345678

a 01234506789

a 1234506789

a M-1234506789

a b-1234506789

a 911234506789

a 911234506789

a 01234506789

a 8989

a 98123456789

a 0123456789

a 1234056789

;

run;

data want(keep=name _new rename=_new=Mob_no);

set t1;

len=length(org);

if len>=10 then _new=substr(org,len-9);

else call missing (_new);

if first(_new) = '0' then call missing (_new);

run;

proc print;run;

Obs name Mob_no

  1 a 1234506789

  2 a 9812345678

  3 a 1234506789

  4 a 1234506789

  5 a 1234506789

  6 a 1234506789

  7 a 1234506789

  8 a 1234506789

  9 a 1234506789

  10 a

  11 a 8123456789

  12 a

  13 a 1234056789

The 11th record remains as it has not excluded by your rules, if it has to go, then 2nd record should go as well.

Haikuo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 1 reply
  • 622 views
  • 0 likes
  • 2 in conversation