BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6
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
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
DanielSantos
Barite | Level 11
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
Flip
Fluorite | Level 6
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
DanielSantos
Barite | Level 11
So many posts at the same time

:D 😄 😄

chandler, you've got the answer.

Cheers from Portugal

Daniel Santos @ www.cgd.pt

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!

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
  • 4 replies
  • 718 views
  • 0 likes
  • 4 in conversation