topic Re: PROC SQL help in SAS Procedures

PROC SQL help

chandler — Fri, 04 Dec 2009 16:12:46 GMT

Is there a way to change a column's data type from numeric to character or character to numeric in a PROC SQL step ? I'm trying to do a join of two tables to get some columns from one table that are not in the other. The column I am doing the join on (customer social security number) is numeric in one table, but character in another. Message was edited by: chandler

Re: PROC SQL help

sbb — Fri, 04 Dec 2009 16:33:55 GMT

Explore using either the PUT or INPUT function in your PROC SQL SELECT statement to generate compatible SAS variables. The SAS support http://support.sas.com/ has references with code examples.

Scott Barry
SBBWorks, Inc.

Re: PROC SQL help

DanielSantos — Fri, 04 Dec 2009 16:37:12 GMT

Yes, this is possible.

You just have to convert one to the other using the appropriate function.

For example, this would work for an inner join:

[pre]proc sql noprint;
create table result as
select a.CUS_SOCIAL_SEC_NUM, a...,
b...
from TABLE_A as a, TABLE_B as b
where a.CUS_SOCIAL_SEC_NUM = inputn(b.CUS_SOCIAL_SEC_NUM,'best.');
quit;[/pre]

inputn function converts a alphanumeric value to a numeric one

see: http://support.sas.com/documentation/cdl/en/sclref/59578/HTML/default/a000162302.htm

or the opposite will do the same:

[pre]
...
where strip(put(a.CUS_SOCIAL_SEC_NUM,best.)) = strip(b.CUS_SOCIAL_SEC_NUM);[/pre]

put function converts a numeric value to a alphanumeric one

see: http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000199354.htm

strip functions just makes sure there is no blanks before and after the value.

Cheers from Portugal

Daniel Santos @ www.cgd.pt

Re: PROC SQL help

Flip — Fri, 04 Dec 2009 16:37:38 GMT

Try doing your join on something like
where a.var1 = put(b.var1, best.) Um, well yea, what Daniel said.

Message was edited by: Flip

Re: PROC SQL help

DanielSantos — Fri, 04 Dec 2009 16:38:46 GMT

So many posts at the same time

:D 😄 😄

chandler, you've got the answer.

Cheers from Portugal

Daniel Santos @ www.cgd.pt