BookmarkSubscribeRSS Feed
TBS1
Fluorite | Level 6

Hi!

I would like to use the proc format in a more dynamic way.

Instead of 

proc format;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

I would like to reference a table I created earlier to make it more dynamic. There are two variables in the table: One is a comparison value and the second contains the desired assignment value.

Something like

 

proc format;
    value points_range
        $referenceTable1.var1 = $referenceTabl1.var2
run; 

Is this possible? Is *this* the way to go? Is there a better way?

Does anyone have a hint for me?

Thanks in advance!

7 REPLIES 7
PaigeMiller
Diamond | Level 26

In this tiny example, making it dynamic doesn't really buy you much. For larger situations, you can make PROC FORMAT more dynamic by creating formats from a SAS data set. Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n1e19y6lrektafn1kj6nbvhus59w.htm

--
Paige Miller
ballardw
Super User

SAS can use a data set to make formats as @PaigeMiller indicates.  The option is CNTLIN=datasetname

There are some caveats:

The variables used by proc format to create have specific names and you must use them.

 

Proc format will also make a data set that can be used to document the formats or to be used. You may want to look at such using a format or two that you have written to see the details.

proc format cntlout=fmtout;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

Basically you provide the name of the format, the type, 'C' for character, 'N' for numeric, start for the beginning of a range, End for the end of the range and Label for the format displayed values.

When you are using options such as Other you will want to look at the HLO variable. that variable is used to indicate if the range includes H (high), L (low) or O (other).  If your ranges exclude one of the end points then you look at the variables SEXCL (Y or N for start exclude ) and EEXCL(Y or N for end exclude ). Your <25 is an EEXCL=Y.

If you get into any of the more complex formats I suggest making small examples like this (don't us a lot of ranges) and create the CNTLOUT set and examine each observation carefully.

 

For Formats with an OTHER  do not actually have Start and End values supplied by you, that will display with the HLO=O set.

TBS1
Fluorite | Level 6

Thank you @ballardw and @PaigeMiller!

Your contributions have led me in the right direction, though they have not entirely answered my cause. Thanks anyway, they really helped me a lot!

PaigeMiller
Diamond | Level 26

@TBS1 

 

More questions? That's what we're here for — well, I can't speak for @ballardw — but ask away!

--
Paige Miller
TBS1
Fluorite | Level 6

@PaigeMiller 

Thank you very much! 😊

Thank you for the offer! 🙂 May I then perhaps refer to another question? I'm not sure if there might not be a better approach than the one I have thought of: https://communities.sas.com/t5/SAS-Programming/Excel-export-with-format/m-p/889420#M351405

Thanks in advance!

Reeza
Super User

One trick is to use CNTLOUT to see the data set that SAS creates when you create a format. 

Then you can see how you need to structure your data set though not all the details are usually required. Here's an example:

*fake data to work with;
data sample;
    do x=1 to 30;
        y=x;
        output;
    end;
run;

*create format;
proc format;
    value points_range 25-high='High' 15-<25='Medium' other='Low';
run;

*export format to dataset;
proc format cntlout=want;
    select points_range;
run;

*view format;
proc print data=want;
    var start end label fmtname type hlo;
run;

*create new dataset from minimal variables;
data example;
    set want;
    fmtname='points_range_ex';
run;

*create format with new name;
proc format cntlin=example;
run;

*display sample data with formats to check output;
proc print data=sample;
    var x y;
    format x points_range. y points_range_ex.;
run;
Tom
Super User Tom
Super User

To make this format.

 

proc format;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

Your dataset needs at a minimum two numbers and three strings.   But to make the ranges you need both the start and the end values.  Since it is easier to remember then to predict the future let's assume the source data looks like:

 

data ranges;
  input cutpoint label $40.;
cards;
25 High
15 Medium
.  Low
;

Since your format is equivalent to this one:

value xx
 low-<15 = 'Low'
 15-<25 = 'Medium'
 25-high = 'High'
 ;

We can generate a CNTLIN type dataset to define such a format from the cut points using code like:

data formats;
  set ranges  end=eof;
  fmtname='points_range';
  start=cutpoint;
  end=lag(cutpoint);
  sexcl='N';
  eexcl='Y';
  if missing(start) then hlo='L';
  if missing(end) then do; 
    hlo='H';
    eexcl='N';
  end;
run;

proc format cntlin=formats ;
run;
----------------------------------------------------------------------------
|                   FORMAT NAME: POINTS_RANGE LENGTH: 6                    |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:   6  FUZZ: STD       |
|--------------------------------------------------------------------------|
|START           |END             |LABEL  (VER. 9.4     15AUG2023:16:48:34)|
|----------------+----------------+----------------------------------------|
|LOW             |              15<Low                                     |
|              15|              25<Medium                                  |
|              25|HIGH            |High                                    |
----------------------------------------------------------------------------


----------------------------------------------------------------------------
|       FORMAT NAME: XX       LENGTH:    6   NUMBER OF VALUES:    3        |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:   6  FUZZ: STD       |
|--------------------------------------------------------------------------|
|START           |END             |LABEL  (VER. V7|V8   15AUG2023:16:48:34)|
|----------------+----------------+----------------------------------------|
|LOW             |              15<Low                                     |
|              15|              25<Medium                                  |
|              25|HIGH            |High                                    |
----------------------------------------------------------------------------


 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1401 views
  • 4 likes
  • 5 in conversation