topic Re: proc sql query help plz in SAS Enterprise Guide

proc sql query help plz

frank1947 — Wed, 27 Mar 2013 17:45:46 GMT

Hi all, i have one variable, 'zcta'. it has 5 digit value and 4 digit value but i want to pull up information where 'zcta' has only 4 digit. How can i do that? ---Thanks.

Re: proc sql query help plz

PGStats — Wed, 27 Mar 2013 17:55:00 GMT

Seems too simple? Add the clause

WHERE ABS(zcta) < 10000

to your query.

Re: proc sql query help plz

frank1947 — Wed, 27 Mar 2013 18:07:48 GMT

that was so logical, writing 10k 🙂 --- Thanks

Re: proc sql query help plz

frank1947 — Wed, 27 Mar 2013 19:57:03 GMT

Hi PGSTATS, can i get hint on this-if you can please?

i have two variable in dataset named 'telno' and 'state', couple of state required zero '0' in front of 'telno' variable. if i wants to add zero '0' for couple of states only, how can i do that? 'telno' is a numeric variable...

Thanks.

Re: proc sql query help plz

DBailey — Wed, 27 Mar 2013 20:01:08 GMT

case when state in ('s1','s2','s3') then '0' end || telno as telno2

Re: proc sql query help plz

PGStats — Wed, 27 Mar 2013 20:34:50 GMT

telno is numeric, cannot be a || operand. Rather :

cats(case when state in ('s1','s2','s3') then '0' end, telno) as telnoChr

Re: proc sql query help plz

frank1947 — Wed, 27 Mar 2013 21:01:46 GMT

@ DBailey- can you please write proc sql step with it - i don't know but i am not just getting it right...

Thanks

Re: proc sql query help plz

DBailey — Wed, 27 Mar 2013 21:03:32 GMT

proc sql;

create table want as

select

state,

telno,

cats(case when state in ('s1','s2','s3') then '0' end, telno) as telnoChr

from

have;

quit;

Re: proc sql query help plz

frank1947 — Wed, 27 Mar 2013 21:17:25 GMT

Thanks you so much DBailey and PGStats. It worked...

appreciate it!