BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JinZhou
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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 = .;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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 = .;
JinZhou
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

ballardw
Super User

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.

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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