FIND numeric in chracter coloumn

[siddharthpeesary]

Hi all,

I have a character coloumn

ex;     A

      121egghhh

      hfghhhdjdj

      5656566

      tt4556hyjh

so I have to check if there is any character in coloumn(A) FLAG as 'fail' else "pass'(i.e 'a' or 'b' .......'z')

and also same if there is any number in coloumn(A)  FLAG as 'fail' else "pass'

can anyone provide me the code  in proc sql 

Thanks in advance

[DartRodrigo · Accepted solution]

Hi,

The following code works very well in sql and SAS Data step

data test;
  length char $ 20;
  input char;
  cards;
121egghhh
hfghhhdjdj
5656566
tt4556hyj
;
run;


data test2;
  set test;
  CourseNum = compress(char, ' ', 'A');
  if compress(char, ' ', 'A') then flg = "fail";
  else flg = "pass"; 
run;

/*SQL*/

proc sql;
  create table test2 as 
     select char,
	 		compress(char, ' ', 'A') as CourseNum,
			case when compress(char, ' ', 'A') then 
			"fail"
			else "pass"
			end as flg
	 from test;
quit;	

Att

[siddharthpeesary]

Thank you so much..

so when we have chracters in coloumn we will get false

                             numbers in coloumn thenwe will get true right.

[PaigeMiller]

---

@siddharthpeesary wrote:

Thank you so much..

so when we have chracters in coloumn we will get false

                             numbers in coloumn thenwe will get true right.

---

Well I guess it doesn't matter if you have the proper solution, but I would still like to understand

Is "false" the same as "fail" and "true" the same as "pass"?

and what happens when you have both numbers and characters in this variable?

[siddharthpeesary]

false is same as fail
true is same as pass
so I have list of balances in a coloumn . so when ever ia m encountered with character mixed with it i should get fail else pass

[PaigeMiller]

---

so I have to check if there is any character in coloumn(A) FLAG as 'fail' else "pass'(i.e 'a' or 'b' .......'z')

and also same if there is any number in coloumn(A)  FLAG as 'fail' else "pass'

---

Seems like you always get "fail" as the answer based on the above explanation.

[siddharthpeesary]

Hi miller,

sorry for the late  reply.

actually i have to validate balance coloumn so that if there is any character in coloumn then i should show a flag as fail else pass

[MikeZdeb]

Hi, how about ...

data test2;
set test;

* input only digits ... course number is numeric;
coursenum = input(compress(char,,'KD'),20.);

* flag any character than is not a number or a space;
flg1 = ifc(findc(char,,'KDS'), 'fail', 'pass');

* flag any alpha character;
flg2 = ifc(anyalpha(char), 'fail', 'pass');

run;

The result appears to fit your rule ... "if there is any character in coloumn then i should show a flag as fail else pass".  The accepted correct solution gives a different result.  Also, use of ...

if compress(char, ' ', 'A') then flg = "fail";

in that solution will give you a note in the LOG about character-to-numeric conversion.

Obs       char       coursenum    flg1    flg2

 1     121egghhh          121     fail    fail
 2     hfghhhdjdj           .     fail    fail
 3     5656566        5656566     pass    pass
 4     tt4556hyj         4556     fail    fail

[siddharthpeesary]

Thank you Mike...! 🙂