BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
DartRodrigo
Lapis Lazuli | Level 10

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

View solution in original post

8 REPLIES 8
DartRodrigo
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thank you so much..

so when we have chracters in coloumn we will get false

                             numbers in coloumn thenwe will get true right.

PaigeMiller
Diamond | Level 26

@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?

--
Paige Miller
siddharthpeesary
Calcite | Level 5
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
Diamond | Level 26


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.

 

--
Paige Miller
siddharthpeesary
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Calcite | Level 5
Thank you Mike...! 🙂

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!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1430 views
  • 2 likes
  • 4 in conversation