BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
I am having trouble getting a user defined format to work.

I have found the following format code
proc format;
picture phone
2000000-9999999 = '999-9999'
9999999<-9999999999 = '999 999-9999'
other = 'miscoded';
run;

I have tried to add the code using EG... Data Create format.
The data is type character - 10 number
I need to get it into a readable format for my report.
I have tried insterting the code where I thought is should go, but I either get an error.
or it doesn't seem to be changing anything.
Once I created it I see the $Phone. as a user defined format and have changed my data properties to use this user defined format... but no luck so far...
Can you help me out... edited to fix formatting


Message was edited by: Chris@SAS
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi, Cheri:
The PICTURE format is expecting that your phone number is numeric, not character. If you are getting an error message similar to this:

ERROR: You are trying to use the numeric format PHONE with the character variable xxxxxxxxx.

Then that is one problem. If, however, your phone number IS numeric and you are not getting your format used, then than is a different problem.

Let's start with some data:

 

Obs name phonenum charphone

1 alan 2222222 2222222
2 bob 9999999 9999999
3 cathy 3103333333 3103333333
4 dave 1234567 1234567
5 eliza 104567890 0104567890


PHONENUM is a numeric variable, you can tell because it is RIGHT-justified and CHARPHONE is a character variable, you can tell because it is LEFT-justified.

Once you DEFINE your format, using the format statement shown above, then you can USE the format. It may not entirely be clear where you define or use the format when you go to insert code in EG. But here's a few notes that may help:
1) when you DEFINE a format, it is a self-contained unit of code that starts with the keywords "PROC FORMAT" and ends with a "RUN". That unit of code must appear in your task code BEFORE you need to use the PICTURE format.
2) when you USE the format, it is generally used WITHIN some other procedure. So the FORMAT statement or a FORMAT= option will appear within some other PROC step (like PROC SQL or PROC PRINT or PROC FREQ). See the example below. Let's say that the data file above is called WORK.TESTPHONE.

proc format;
picture phone
2000000-9999999 = '999-9999'
9999999<-9999999999 = '999 999-9999'
other = 'miscoded';
run;

** now, use the format with a format statement;
proc print data=work.testphone;
title 'Testing Phone Number Picture Format';
var name phonenum charphone;
format phonenum phone. ;
run;


As you can see...the PROC FORMAT step is self-contained and sits OUTSIDE the PROC PRINT step.
This is code which you would normally generate with the LIST DATA task in EG.
However, the FORMAT statement, which USES the PHONE. format sits INSIDE the PROC PRINT step.

You cannot use the PHONE. format with the CHARPHONE variable, because PHONE. is a numeric format, and SAS will give you an error message if you try to use a numeric format with a character variable. So, what do you do if you have a character variable and you need to apply a PICTURE format to it??? In that case, you have to convert the format using an INPUT statement (this is the code for a DATA step program):

convert_ph = input(charphone,10.);


The new variable CONVERT_PH is now a numeric variable and could be formatted with the PHONE. format. The following example with PROC SQL shows creating a new numeric variable from CHARPHONE and assigning the format within PROC SQL.

 

** proc format step would go here;
** THEN, use the format with PROC SQL in a query;

proc sql;
create table work.newphone as
select name,
phonenum format=phone.,
input(charphone,10.) as newphnum format=phone.
from work.testphone
order by name;
quit;

proc print data=work.newphone;
title 'Data made with SQL -- do not need format statement here';
title2 'because it was assigned in the SQL step above';
var name phonenum newphnum;
run;


In this example, I don't show the PROC FORMAT step again, but in order to run this second bit of code, you would need to have created the format in the same session or have the PROC FORMAT code, as a self-contained unit sitting OUTSIDE the PROC SQL step. The method of creating a new variable inside the PROC SQL is only a bit different from the assignment above. But basically, you are still using the INPUT function to convert CHARPHONE into a new numeric variable called NEWPHNUM. The PROC PRINT results are shown below for both datasets.

cynthia

View solution in original post

3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi, Cheri:
The < sign is causing the rest of your message to disappear. One way around it is to use &-lt-; (ampersand,lt,semi-colon -- all together, no spaces) for the Less than sign and to use &-gt-; (amtersand, gt,semi-colon -- all together, no spaces for the Greater than sign.

Look at the first example in this Tech Support note, which contains a PICTURE format for a phone number and another for a fax number:
http://support.sas.com/kb/25/301.html

cynthia
deleted_user
Not applicable
PROC SQL;
%_SASTASK_DROPDS(ENROLL.CFMT);
QUIT;


PROC FORMAT
LIB=ENROLL
;
VALUE $Phone
"" = "phone";

I tried what you suggested with the &<;
and then I tried the following...
Maybe I'm just not understanding how to do the user defined format.
I trying to use the option in Data... Create Format.
I'm inserting the user defined code....
and when I am done and Preview the code... I get the following.
I then run my query that has a field defined using the $phone. format
And I also tried using it in my LIST Data... but neither of them show the format I want... I just get a string of numbers... all together....

/* Start of custom user code. */
picture phone 0000000000-9999999999='0999)999-9999' (prefix='(');
/* End of custom user code. */
RUN;

/* -------------------------------------------------------------------
End of task code.
------------------------------------------------------------------- */
RUN; QUIT;
TITLE; FOOTNOTE;
Cynthia_sas
SAS Super FREQ

Hi, Cheri:
The PICTURE format is expecting that your phone number is numeric, not character. If you are getting an error message similar to this:

ERROR: You are trying to use the numeric format PHONE with the character variable xxxxxxxxx.

Then that is one problem. If, however, your phone number IS numeric and you are not getting your format used, then than is a different problem.

Let's start with some data:

 

Obs name phonenum charphone

1 alan 2222222 2222222
2 bob 9999999 9999999
3 cathy 3103333333 3103333333
4 dave 1234567 1234567
5 eliza 104567890 0104567890


PHONENUM is a numeric variable, you can tell because it is RIGHT-justified and CHARPHONE is a character variable, you can tell because it is LEFT-justified.

Once you DEFINE your format, using the format statement shown above, then you can USE the format. It may not entirely be clear where you define or use the format when you go to insert code in EG. But here's a few notes that may help:
1) when you DEFINE a format, it is a self-contained unit of code that starts with the keywords "PROC FORMAT" and ends with a "RUN". That unit of code must appear in your task code BEFORE you need to use the PICTURE format.
2) when you USE the format, it is generally used WITHIN some other procedure. So the FORMAT statement or a FORMAT= option will appear within some other PROC step (like PROC SQL or PROC PRINT or PROC FREQ). See the example below. Let's say that the data file above is called WORK.TESTPHONE.

proc format;
picture phone
2000000-9999999 = '999-9999'
9999999<-9999999999 = '999 999-9999'
other = 'miscoded';
run;

** now, use the format with a format statement;
proc print data=work.testphone;
title 'Testing Phone Number Picture Format';
var name phonenum charphone;
format phonenum phone. ;
run;


As you can see...the PROC FORMAT step is self-contained and sits OUTSIDE the PROC PRINT step.
This is code which you would normally generate with the LIST DATA task in EG.
However, the FORMAT statement, which USES the PHONE. format sits INSIDE the PROC PRINT step.

You cannot use the PHONE. format with the CHARPHONE variable, because PHONE. is a numeric format, and SAS will give you an error message if you try to use a numeric format with a character variable. So, what do you do if you have a character variable and you need to apply a PICTURE format to it??? In that case, you have to convert the format using an INPUT statement (this is the code for a DATA step program):

convert_ph = input(charphone,10.);


The new variable CONVERT_PH is now a numeric variable and could be formatted with the PHONE. format. The following example with PROC SQL shows creating a new numeric variable from CHARPHONE and assigning the format within PROC SQL.

 

** proc format step would go here;
** THEN, use the format with PROC SQL in a query;

proc sql;
create table work.newphone as
select name,
phonenum format=phone.,
input(charphone,10.) as newphnum format=phone.
from work.testphone
order by name;
quit;

proc print data=work.newphone;
title 'Data made with SQL -- do not need format statement here';
title2 'because it was assigned in the SQL step above';
var name phonenum newphnum;
run;


In this example, I don't show the PROC FORMAT step again, but in order to run this second bit of code, you would need to have created the format in the same session or have the PROC FORMAT code, as a self-contained unit sitting OUTSIDE the PROC SQL step. The method of creating a new variable inside the PROC SQL is only a bit different from the assignment above. But basically, you are still using the INPUT function to convert CHARPHONE into a new numeric variable called NEWPHNUM. The PROC PRINT results are shown below for both datasets.

cynthia

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 3 replies
  • 6642 views
  • 1 like
  • 2 in conversation