topic Re: [Question] Use Proc Format to replace if-then/else in SAS Procedures

[Question] Use Proc Format to replace if-then/else

JinZhou — Tue, 18 Oct 2016 06:11:56 GMT

Hello, guys.

I have a question about the PROC format, and I'm using SAS 9.4 under windows.

Below is my original data code.

DATA one;
INPUT A :10. B :$;
CARDS;
1 A
1.1 B
1.3 C
10 D
;
RUN;

DATA two;
SET one;
IF A>2 then A=.;
RUN;

The objective is to check if A is in the range of (0,2) or not, if A isn't in the range, let A be null.

Below was what I thought might work using PROC FORMAT.

DATA one;
INPUT A :10. B :$;
CARDS;
1 A
1.1 B
1.3 C
10 D
;
RUN;

Proc format;
Value num
low-0=.
2-high=.
other=.
;
RUN;

Data three;
SET one;
FORMAT A num.;
RUN;

Unfortunately, it didn't.

Any one has any ideas?

Thank you.

Re: [Question] Use Proc Format to replace if-then/else

Kurt_Bremser — Tue, 18 Oct 2016 07:06:06 GMT

First, assigning a format will not change the value, so for purposes like using it in a condition, the variable stays the same.

In your format, the result will be a dot in all cases, so that won't work either. Also keep in mind that the result of a value format is always of type character, never numeric.

Your simple

if a > 2 then a = .;

is the best solution. Stay with it.

Edit: and if you also want to exclude values < 0, do

if a > 2 or a < 0 then a = .;

Re: [Question] Use Proc Format to replace if-then/else

JinZhou — Tue, 18 Oct 2016 07:12:31 GMT

Thank you Kurt. Your reply is always fast and straight.

But by the way, I have to a massive data cleaning process for each variable like this one. (Mass = 900+)

These Variables include character, number+character strings(like license), numbers strings(like phone number), intervals, etc..

Is there a quick and simple way I can use? Just like proc format, I could just define it in one bulk step with my data dictionary, and apply it over and over to each variable whenever I need.

Thank you a lot Kurt.

Re: [Question] Use Proc Format to replace if-then/else

Kurt_Bremser — Tue, 18 Oct 2016 08:23:39 GMT

As I said, just applying a format won't solve your issue (reliably), as the values stay the same. You could use an informat in an input() function, but I'd rather write a macro for the correction, and call that macro with call execute() from a datastep that uses a dataset created from sashelp.vcolumn.

Creating lots of formats might overload your internal format dictionary, and that may well cause performance issues.

Re: [Question] Use Proc Format to replace if-then/else

ballardw — Tue, 18 Oct 2016 14:36:54 GMT

A FORMAT may not be the answer but an INFORMAT might if you want to change numeric to numeric. Then use an INPUT statement with text value of your variable and the informat.

proc format library=work;
invalue dummy
0  - < 10 = 1
10 - < 100 = 2
100 - high = 3;
run;

data example;
   input x;
datalines;
1
9
15
1233
4444444
;

data want;
   set example;
   x = input(put(x,best12.),dummy.);
run;

You could actually use x instead of put(x,best12.) but you'll get "NOTE: Numeric values have been converted to character values at the places given by:" in the log.