Creating a Range Format from a Dataset

[angeliquec]

Hi, I have this lookup table (range), which I intend to use as a format for another table (have).

data range;
input low high desc $8.;
cards;
-999999 1 Low
2 3 Medium
4 999999 High
;
run;

data have;
input num;
cards;
-1
0
5
;
run;

I'd also prefer for the low range, to include all values <= 1, and the high range to include all values > 4.

How is this possible?

[RW9]

You can use LOW and HIGH to represent below and above respectively, as this code demonstrates:

proc format cntlout=temp;
  value test
    low-1=23
    2-5=45
    6-high=99;
run;

The dataset temp will show the output.

[Astounding · Accepted solution]

Here are changes to make, for this to be suitable for creating a format:

data range;
input start end label $8.;

if label='Low' then HLO='L';

else if label='High' then HLO='H';

fmtname='Range';
cards;
-999999 1 Low
2 3 Medium
4 999999 High
;

Then you can create a format using:

proc format cntlin=range;

run;

And you can use the format when processing HAVE, by adding:

format num range.;

A few notes:

• The variable names have been changed.  START, END, LABEL, and FMTNAME are required names.
• Note that you have gaps here, for non-integer values.
• When HLO="L", that tells PROC FORMAT to ignore the START value, and consider "Low" to be the start of the range.
• Similarly, when HLO="H", that tells PROC FORMAT to ignore the END value, and consider "High" to be end of the range.
• The name of the format (range.) comes from the FMTNAME variable, not from the name of the CNTLIN= data set.