topic Re: Creating a Range Format from a Dataset in SAS Programming

Creating a Range Format from a Dataset

angeliquec — Mon, 10 Oct 2016 15:09:51 GMT

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 — Mon, 10 Oct 2016 15:14:24 GMT

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 — Mon, 10 Oct 2016 15:25:56 GMT

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.