BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
In a stored process I'm making, I have a character field coming in that contains Social Security numbers. Can I use the format=SSN11. format line against it, or does my character field have to be converted to numeric first?

Second question: In Proc Sql, can I use an input command (XYZ=INPUT(ABC,$11) to convert the character field to numeric, or does that have to be done in a data step?

Is there an "easy" way to do what I'm trying to accomplish?

Thanks in advance so much!
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:
Look in the documentation for the topics:
Functions and CALL Routines
INPUT Function

The bottom line is that to convert a CHARACTER variable to a number, you use the INPUT function like this:

 

newnumvar = INPUT(oldcharvar,numeric-informat.); (DATA step)
OR
INPUT(oldcharvar,numeric-informat.) as newnumvar (SQL)

So you were ALMOST right in your INPUT statement. Think of it this way, in order to turn your old character variable into a NEW numeric variable, you have to give the NUMERIC INformat to use.

See the example below.
cynthia

** make some data;
** ssnc is a 9 position character variable with SSN;
** ssn is a numeric variable;
data testit;
name = 'alan';
ssn = 123456789;
ssnc = '123456789';
output;

name = 'bob';
ssn = 002334444;
ssnc = '002334444';
output;
run;

ods listing;

proc sql ;
select name, ssn format=ssn11., ssnc,
input(ssnc,9.) as newssn format=ssn11.
from work.testit;
quit;


and the output:

name ssn ssnc newssn
-----------------------------------------
alan 123-45-6789 123456789 123-45-6789
bob 002-33-4444 002334444 002-33-4444

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

Hi:
Look in the documentation for the topics:
Functions and CALL Routines
INPUT Function

The bottom line is that to convert a CHARACTER variable to a number, you use the INPUT function like this:

 

newnumvar = INPUT(oldcharvar,numeric-informat.); (DATA step)
OR
INPUT(oldcharvar,numeric-informat.) as newnumvar (SQL)

So you were ALMOST right in your INPUT statement. Think of it this way, in order to turn your old character variable into a NEW numeric variable, you have to give the NUMERIC INformat to use.

See the example below.
cynthia

** make some data;
** ssnc is a 9 position character variable with SSN;
** ssn is a numeric variable;
data testit;
name = 'alan';
ssn = 123456789;
ssnc = '123456789';
output;

name = 'bob';
ssn = 002334444;
ssnc = '002334444';
output;
run;

ods listing;

proc sql ;
select name, ssn format=ssn11., ssnc,
input(ssnc,9.) as newssn format=ssn11.
from work.testit;
quit;


and the output:

name ssn ssnc newssn
-----------------------------------------
alan 123-45-6789 123456789 123-45-6789
bob 002-33-4444 002334444 002-33-4444
deleted_user
Not applicable
So close, yet so far! Thanks for the information..... that'll come in handy for many Proc Sql's to come.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 8859 views
  • 0 likes
  • 2 in conversation