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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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.

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
  • 2 replies
  • 8236 views
  • 0 likes
  • 2 in conversation