topic Handle unexpected characters with Proc Format in SAS Procedures

Handle unexpected characters with Proc Format

anjgupta — Wed, 31 Aug 2011 19:10:13 GMT

Hi,

I'm using the following format:

proc format;

invalue month_pncf (default=2)

0 = 88

99 = .U

. = .U

other = _same_;

run;

expecting the raw data to be in numeric form. However, when the incoming data is a dash '-' in the .dat file, I get a value of dot '.' for the formatted data. Is there some way to make the proc format more robust so that dashes or other characters return .U? It seems the other=_same_ isn't handling non-numeric characters.

The log has an error of "Invalid data for pnc_mth in line xxx"

Ideally I'd like to do this solely with proc format - changing from an old way in which we scanned the data for 'illegal' vars prior to formatting.

Thank you,

Anjali

Handle unexpected characters with Proc Format

Tom — Wed, 31 Aug 2011 19:18:33 GMT

Just add it to your INFORMAT definition. You will need to add the quotes that you have omitted for the other values.

proc format;

invalue month_pncf (default=2)

'0' = 88

'99','.','-' = .U

other = _same_;

run;

Handle unexpected characters with Proc Format

FriedEgg — Wed, 31 Aug 2011 19:19:02 GMT

proc format;

invalue numfmt (default=2)

0 = 88

99 = .U

. = .U

other = _same_;

run;

data input;

input num numfmt.;

if _ERROR_=1 then do; num=.U; _ERROR_=0; end;

datalines;

;

run;

You will still get the note about invalid data, but not an error and the value will be equal to .U when an invalid value is encountered.

Handle unexpected characters with Proc Format

data_null__ — Wed, 31 Aug 2011 19:26:52 GMT

Seems like you have two choices. Define the range for _SAME_ to include values from 1-98 and let OTHER=.U or use the INVALIDDATA option.

options invaliddata=U;

Handle unexpected characters with Proc Format

anjgupta — Wed, 31 Aug 2011 19:54:46 GMT

I assume the OPTIONS way will affect the entire program. And the error handling way is particular to each variable. I have a lot of vars to read in for a particular infile - is there a middle way to have the error/invaliddata handled within 1 datastep for all incoming vars?

Thank you

Handle unexpected characters with Proc Format

Tom — Wed, 31 Aug 2011 20:07:34 GMT

You could fully specify the valid range.

'1','2','3','4','5','6','7','8','9'

,'10' - '19'

,'20' - '29'

,'30' - '39'

....

= _same_

Handle unexpected characters with Proc Format

anjgupta — Wed, 31 Aug 2011 20:20:11 GMT

I could do that for month - which is a small range. But I also have items like birthweight in grams and then the range we accept is infinite. The

options invaliddata=U; didn't accept .U which is the desired formatted value. Still trying.

Handle unexpected characters with Proc Format

Tom — Wed, 31 Aug 2011 20:24:36 GMT

You could use the automatic variable _ERROR_ and the ?? operator to suppress the error messages.

But I think you would need to read each variable separately.

input num ??numfmt. @ ;

if _error_ then numfmt=.U ;

_error_=0;

...

Handle unexpected characters with Proc Format

Tom — Wed, 31 Aug 2011 20:27:46 GMT

Actuall better still just use ?? and leave the value missing?

Handle unexpected characters with Proc Format

FriedEgg — Wed, 31 Aug 2011 20:45:30 GMT

proc format;

invalue myfmt (default=2)

0 = 88

99 = .U

other = _same_;

run;

data want;

input mynum :??myfmt. @@; /* nice way to suppress the unnecessary notes from my previous example, thanks Tom */

if mynum=. then mynum=.U;

putlog mynum=;

cards4;

0 1 2

Y T D

! . (

99 0 ;

;;;;

run;

Handle unexpected characters with Proc Format

data_null__ — Wed, 31 Aug 2011 20:55:37 GMT

In the INVALIDDATA option you specify the missing value without the dot. Do you have access to online help?

Syntax

INVALIDDATA='character'

Syntax Description

'character'

specifies the value to be assigned, which can be a letter (A through Z, a through z), a period (.), or an underscore (_). The default value is a period.

Handle unexpected characters with Proc Format

anjgupta — Wed, 07 Sep 2011 14:38:46 GMT

I'll try just 'U' - but I need it to indicate the missing value of .U (numeric) and not the character 'U'.

Thank you.

Handle unexpected characters with Proc Format

anjgupta — Wed, 07 Sep 2011 15:03:37 GMT

Hi,

I'm trying to find some documentation on ? and ?? - new to me. Seems I can add ? or ?? to my invalue statements for all or some variables that might result in errors. FriedEgg uses a colon ":??" but Tom did not. Is one 'correct'?

If you have any links to help me understand this operator better - great. Still searching.

Handle unexpected characters with Proc Format

art297 — Wed, 07 Sep 2011 15:21:39 GMT

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000180357.htm

Handle unexpected characters with Proc Format

anjgupta — Wed, 07 Sep 2011 15:57:21 GMT

Thank you - found it between my post and yours.

Am I correct that if my current macro reads data as:

628 %create_input_string_D3(statevars, birth)

MPRINT(CREATE_INPUT_STRING_D3): input

MPRINT(CREATE_INPUT_STRING_D3): #1 @003 certnum 6.

MPRINT(CREATE_INPUT_STRING_D3): #1 @009 mm_dob monthf.

MPRINT(CREATE_INPUT_STRING_D3): #1 @011 dd_dob 2.

MPRINT(CREATE_INPUT_STRING_D3): #1 @015 sex u_nine.

MPRINT(CREATE_INPUT_STRING_D3): #1 @016 b_state 2.

MPRINT(CREATE_INPUT_STRING_D3): #1 @031 m_state 2.

MPRINT(CREATE_INPUT_STRING_D3): #1 @018 b_county $char3.

MPRINT(CREATE_INPUT_STRING_D3): #1 @021 pob u_nine.

MPRINT(CREATE_INPUT_STRING_D3): #1 @023 mm_mdob monthf.

MPRINT(CREATE_INPUT_STRING_D3): #1 @025 dd_mdob 2.

MPRINT(CREATE_INPUT_STRING_D3): #1 @033 mom_res $char3.

MPRINT(CREATE_INPUT_STRING_D3): #1 @036 mm_pdob monthf.

MPRINT(CREATE_INPUT_STRING_D3): #1 @038 dd_pdob 2.

MPRINT(CREATE_INPUT_STRING_D3): #1 @042 hisp_bc hisp_bc_xx.

etc ...

I would need a separate input statement for each variable in order to handle the errors? That's a change - but probably possible.

Re: Handle unexpected characters with Proc Format

FriedEgg — Wed, 07 Sep 2011 16:17:27 GMT

14         proc format;
15         invalue myfmt (default=2)
16           0  = 88
17           99 = .U
18           other = _same_;
NOTE: Informat MYFMT is already on the library.
NOTE: Informat MYFMT has been output.
19         run;
NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
20         
21         data _null_;
22         input @1 mynum  ??myfmt.
23               @1 orig   $char2.
24               @3 mychar $char1.;
25         if mynum=. then mynum=.U;
26         putlog orig= mynum=;
27         cards4;
orig=0 mynum=88
orig=1 mynum=1
orig=Y mynum=U
orig=T mynum=U
orig=. mynum=U
orig=: mynum=U
orig=99 mynum=U
orig=9 mynum=9
orig=1. mynum=1
NOTE: The data set WORK.WANT has 9 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
37         ;;;;
38         run;

Handle unexpected characters with Proc Format

anjgupta — Wed, 07 Sep 2011 17:09:52 GMT

Thank you for the coding and output. The trick is that the macro reads in the varname, length, etc - and it would be ideal to use the varname (before it's replaced in the next iteration of the loop) while I have 'it' - to avoid hard coding later. That is, at the same time I read in a particular variable I'd like to address any _error_ issues.

May not be easy or possible.

Handle unexpected characters with Proc Format

Tom — Wed, 07 Sep 2011 18:20:37 GMT

More documentation is available on the help page for the INPUT statement. There you can find the information on the : & and ~ in the section on Modified List Input.

Handle unexpected characters with Proc Format

Tom — Wed, 07 Sep 2011 18:25:58 GMT

If your macro was generating one INPUT statement you can split it up by using the trailing @.

Instead of :

#1 @003 certnum 6.

#1 @009 mm_dob monthf.

#1 @011 dd_dob 2.

Generate a complete INPUT statement with a trailing @.

input #1 @003 certnum ?6. @;

if _error_ then put 'Invalid data for variable ' "certnum" ;

_error_=0;

input #1 @009 mm_dob ?monthf. @;

if _error_ then put 'Invalid data for variable ' "mm_dob" ;

_error_=0;

Handle unexpected characters with Proc Format

FriedEgg — Wed, 07 Sep 2011 20:04:59 GMT

14 %macro layout;

15 #1 @1 mynum1 ??myfmt.

16 #1 @3 mychar $char2.

17 #2 @5 mynum2 ??myfmt.

18 %mend;

20 proc format;

21 invalue myfmt (default=2)

22 0 = 88

23 99 = .U

24 other = _same_;

NOTE: Informat MYFMT is already on the library.

NOTE: Informat MYFMT has been output.

25 run;

NOTE: PROCEDURE FORMAT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

27 data fake;

28 input %layout;

29 cards;

NOTE: The data set WORK.FAKE has 0 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

30 ;

31 run;

33 proc sql noprint;

34 select 'if ' !! strip(name) !! '=. then ' !! strip(name) !! '=.U' into :missing separated by '; '

35 from sashelp.vcolumn

36 where libname='WORK' and memname='FAKE' and type='num';

37 run;

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2 The SAS System 10:48 Wednesday, September 7, 2011

39 data _null_;

40 input #1 @1 orig1 $2.

41 #2 @5 orig2 $2.

42 %layout;

43 &missing;

44 put orig1= mynum1= mychar= orig2= mynum2=;

45 cards;

orig1=1 mynum1=1 mychar=A orig2=9 mynum2=9

orig1=99 mynum1=U mychar=C orig2=0 mynum2=88

orig1=62 mynum1=62 mychar=E orig2=/ mynum2=U

orig1=[ mynum1=U mychar=[ orig2= mynum2=U

orig1=.A mynum1=A mychar=. orig2= mynum2=U

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

56 ;

57 run;

59 %put %bquote(&missing);

if mynum1=. then mynum1=.U; if mynum2=. then mynum2=.U