topic Re: Proc Format: Using keywords in control input tables in SAS Programming

Proc Format: Using keywords in control input tables

kelbrosna — Thu, 22 Jul 2021 01:55:25 GMT

Hello,

I'm trying to use proc format to read in a table using the following code:

data test_storm_categories;
	length Start $8  End $8 Label $15;
	retain fmtName "testStormFormat" type "n";
	input Start $ End $ Label $;
	infile datalines dlm = ",";
	datalines;
	low,63,No Category
	64,82,Category 1 
	83,95,Category 2 	
	96,112,Category 3 	
	113,136,Category 4 
	137,high,Category 5
	;
	run;

proc format cntlIn = test_storm_categories;
	run;

But, I'm getting the following error:

ERROR: Cannot mix missing and nonmissing values in the same range: .-63.

Can someone explain to me why 'low' isn't being read as a keyword here?

Thanks,

kelbrosna

PS. Here are some more details:

1. Version number (per proc setinit) is 9.04.01M6P110718, and I'm using SAS Studio.

2. I'm aware that you can specify keywords for ranges using an 'HLO' column, and that fix works for me. I don't need this code to work, I'm just curious why it fails.

3. This code is based on an exercise from the SAS Programming 2: Data Manipulation Techniques course notes (Lesson 4.2, page 4-20 step 2).

a. The exercise uses a premade table (storm_categories.sas7bdat) from the course data (which I would include, but I don't think I'm allowed to share it), and I also read in that table using the following code:

data storm_categories;
	set pg2.storm_categories (rename=
	(Low = Start High = End Category = Label));
	retain fmtName "stormFormat";
	run;

proc format cntlIn = storm_categories;
	run;

This code runs as expected, and it does not give any errors. What's perplexing about this is that the test_storm_categories dataset from the datalines statement in the original code is seemingly identical to this storm_categories dataset, but their behavior is different.

b. When I export and re-import the storm_categories.sas7bdat table as a CSV file (using proc export and proc import), then perform the necessary operations and feed it to proc format, I get the same error as that from test_storm_categories. This makes me think that there may be some kind of hidden metadata in the storm_categories.sas7bdat table that matters. Is that a possibility?

4. The reason why I think the 'low' keyword is being misread is because when changed to a number, e.g. 40, no error is raised. However, the 'high' keyword doesn't raise any error, even when left unchanged. My current understanding of what is happening is that the character string "low" is being read as a missing numeric value, but I'm not sure why this is.

Re: Proc Format: Using keywords in control input tables

Reeza — Thu, 22 Jul 2021 02:13:36 GMT

High/Low keyword require the HLO flag set. Because you could have a value of High/Low you wanted to recode as well, in fact those are common in informants/formats so you need to tell SAS to not interpret it as value but as the keyword, which you do via the HLO flag.

One quick way I test questions like this is to generate the format manually and then pipe the format to a CNTLOUT dataset and check the data set it creates.

proc format;
value testStormFormat
low - 63 = "No Category"
64 - 82 = "Category 1"
83 - 95 = "Category 2"
96 - 112 = "Category 3"
112 - 136 = "Category 4"
137 - High = "Category 5";
run;

proc format cntlout=demo;
select testStormFormat;
run;

proc print data=demo;
run;

Regarding 3B -> Since you're not sharing the code from the storm program I cannot comment on that but I'm guessing it does set the HLO flag somewhere.

From the documentation:

If you specify START='LOW', and the HLO variable does not contain 'L', then the literal value of LOW is used. If you specify START='OTHER', and the HLO variable does not contain 'O', then the literal value of OTHER is used. If you specify END='HIGH', and the HLO variable does not contain 'H', then the literal value of HIGH is used.

@kelbrosna wrote:

Hello,

I'm trying to use proc format to read in a table using the following code:
data test_storm_categories;
	length Start $8  End $8 Label $15;
	retain fmtName "testStormFormat" type "n";
	input Start $ End $ Label $;
	infile datalines dlm = ",";
	datalines;
	low,63,No Category
	64,82,Category 1 
	83,95,Category 2 	
	96,112,Category 3 	
	113,136,Category 4 
	137,high,Category 5
	;
	run;

proc format cntlIn = test_storm_categories;
	run;
But, I'm getting the following error:
ERROR: Cannot mix missing and nonmissing values in the same range: .-63.
Can someone explain to me why 'low' isn't being read as a keyword here?

Thanks,

kelbrosna

PS. Here are some more details:

1. Version number (per proc setinit) is 9.04.01M6P110718, and I'm using SAS Studio.

2. I'm aware that you can specify keywords for ranges using an 'HLO' column, and that fix works for me. I don't need this code to work, I'm just curious why it fails.

3. This code is based on an exercise from the SAS Programming 2: Data Manipulation Techniques course notes (Lesson 4.2, page 4-20 step 2).

a. The exercise uses a premade table (storm_categories.sas7bdat) from the course data (which I would include, but I don't think I'm allowed to share it), and I also read in that table using the following code:
data storm_categories;
	set pg2.storm_categories (rename=
	(Low = Start High = End Category = Label));
	retain fmtName "stormFormat";
	run;

proc format cntlIn = storm_categories;
	run;
This code runs as expected, and it does not give any errors. What's perplexing about this is that the test_storm_categories dataset from the datalines statement in the original code is seemingly identical to this storm_categories dataset, but their behavior is different.

b. When I export and re-import the storm_categories.sas7bdat table as a CSV file (using proc export and proc import), then perform the necessary operations and feed it to proc format, I get the same error as that from test_storm_categories. This makes me think that there may be some kind of hidden metadata in the storm_categories.sas7bdat table that matters. Is that a possibility?

4. The reason why I think the 'low' keyword is being misread is because when changed to a number, e.g. 40, no error is raised. However, the 'high' keyword doesn't raise any error, even when left unchanged. My current understanding of what is happening is that the character string "low" is being read as a missing numeric value, but I'm not sure why this is.

Re: Proc Format: Using keywords in control input tables

Reeza — Thu, 22 Jul 2021 02:16:46 GMT

FYI - if you're paying for the course you can ask SAS about 3a/b.

Re: Proc Format: Using keywords in control input tables

Astounding — Thu, 22 Jul 2021 13:24:49 GMT

Once you digest Reeza's explanation, it is possible you just need to add a single statement to your DATA step:

if start="low" then HLO = "L";

When HLO is set to "L", SAS ignores the value of START and assumes that you want to use the lowest undefined value as the beginning of your range.

Re: Proc Format: Using keywords in control input tables

kelbrosna — Thu, 22 Jul 2021 22:26:41 GMT

From the documentation:

"If you are creating a format with the CNTLIN= option, the value of LOW or OTHER for the START variable and the value of HIGH for the END variable are interpreted as their corresponding keywords of LOW, OTHER, or HIGH. This interpretation occurs whether you specify the values in uppercase or lowercase. If you want the explicit values of LOW, OTHER, or HIGH to be used, then specify the HLO variable with the values as described below."

Based on the wording, if there is no HLO variable in the CNTLIN table, shouldn't SAS be expected to interpret "low" and "high" as keywords?

Based on the error in the original post, it seems that "low" is being interpreted as the literal value "low". However, as mentioned in part 4, when the value "low" is changed to 40 in the input table, SAS seems to interpret "high" as a keyword, as shown by the code below:

data test_storm_categories;
	length Start $8  End $8 Label $15;
	retain fmtName "testStormFormat" type "n";
	input Start $ End $ Label $;
	infile datalines dlm = ",";
	datalines;
	40,63,No Category
	64,82,Category 1 
	83,95,Category 2 	
	96,112,Category 3 	
	113,136,Category 4 
	137,high,Category 5
	;
	run;

proc format cntlIn = test_storm_categories fmtlib;
	select testStormFormat;
	run;
	
proc format cntlOut = demo;
	select testStormFormat;
	run;

The CNTLOUT table's HLO column correctly assigns a value of "H" for the row with "high". Is there any particular reason why "high" and "low" should be interpreted differently by SAS?

Re: Proc Format: Using keywords in control input tables

Reeza — Thu, 22 Jul 2021 22:55:45 GMT

The paragraph is a bit ambiguous, and the paragraph that follows contradicts that...and the SAS behaviour aligns with the second paragraph.

Re: Proc Format: Using keywords in control input tables

Reeza — Thu, 22 Jul 2021 22:56:40 GMT

And there's always a HLO value in the data set, SAS creates it automatically and it's blank.

Re: Proc Format: Using keywords in control input tables

kelbrosna — Fri, 23 Jul 2021 03:32:59 GMT

Two questions:

1. Is there a way to get SAS to display hidden columns?

2. If the HLO column is blank, why does "high" get interpreted as a keyword?

Re: Proc Format: Using keywords in control input tables

Reeza — Fri, 23 Jul 2021 14:42:39 GMT

As mentioned previously, CNTLOUT. For your second question read the paragraph exactly AFTER the one you posted initially that describes how to set HIGH as a literal value, ie if you had HIGH, LOW, MEDIUM as text values and wanted to recode those to 1, 2, 3 via a format, how would you pass those values to SAS?

proc format cntlout=mydata;
select formatName;
run;

Re: Proc Format: Using keywords in control input tables

Reeza — Fri, 23 Jul 2021 14:46:37 GMT

FROM the documentation as well: If you specify END='HIGH', and the HLO variable does not contain 'H', then the literal value of HIGH is used.

Blanks are not H.

Re: Proc Format: Using keywords in control input tables

kelbrosna — Wed, 28 Jul 2021 05:36:38 GMT

From my previous reply:

data test_storm_categories;
	length Start $8  End $8 Label $15;
	retain fmtName "testStormFormat" type "n";
	input Start $ End $ Label $;
	infile datalines dlm = ",";
	datalines;
	40,63,No Category
	64,82,Category 1 
	83,95,Category 2 	
	96,112,Category 3 	
	113,136,Category 4 
	137,high,Category 5
	;
	run;

proc format cntlIn = test_storm_categories fmtlib;
	select testStormFormat;
	run;
	
proc format cntlOut = demo;
	select testStormFormat;
	run;

proc print data = demo;
	run;

Running this code, we get a valid format, and the auto-generated HLO column has the value "H" in the row where the END column had value "high". Based on what you said above, the auto-generated HLO column should be blank. The fact that this code generates an HLO column with a non-blank entry seems to contradict that claim.

When you run the above code, do you get a different result?

Re: Proc Format: Using keywords in control input tables

Reeza — Wed, 28 Jul 2021 14:22:15 GMT

Open a ticket with SAS tech support.

Re: Proc Format: Using keywords in control input tables

Tom — Wed, 28 Jul 2021 15:10:39 GMT

If you don't want it to interpret 'high' as meaning HIGH then you would need to create an character format instead of a numeric format.

If you want to make a valid CNTLIN dataset that is clearer to you and more closely matches the CNTLOUT dataset then include the HLO variable.

data test_storm_categories;
  retain fmtName "testStormFormat" type "n" ;
  input Start $ End $ Label $15.;
  length hlo $3;
  if start='other' then do; hlo='O'; start=' '; end=' '; end;
  if start='low' then do; hlo='L'; start=' '; end;
  if end='high' then do; hlo=cats(hlo,'H'); end=' '; end;
datalines;
40 63 No Category
64 82 Category 1
83 95 Category 2
96 112 Category 3
113 136 Category 4
137 high Category 5
other . Invalid number
;

proc format cntlIn = test_storm_categories fmtlib cntlout=demo;
  select testStormFormat;
run;

proc print data=demo;
  var fmtname type start end hlo label;
run;

DEMO output

Obs        FMTNAME        TYPE         START                END           HLO        LABEL

 1     TESTSTORMFORMAT     N                    40                  63           No Category
 2     TESTSTORMFORMAT     N                    64                  82           Category 1
 3     TESTSTORMFORMAT     N                    83                  95           Category 2
 4     TESTSTORMFORMAT     N                    96                 112           Category 3
 5     TESTSTORMFORMAT     N                   113                 136           Category 4
 6     TESTSTORMFORMAT     N                   137    HIGH                 H     Category 5
 7     TESTSTORMFORMAT     N      **OTHER**           **OTHER**            O     Invalid number

Re: Proc Format: Using keywords in control input tables

Reeza — Wed, 28 Jul 2021 15:10:35 GMT

High seems to work correctly, but Low does not - see first set of code ergo my recommendation to talk to Tech Support now.

Re: Proc Format: Using keywords in control input tables

Tom — Wed, 28 Jul 2021 15:18:37 GMT

Low and OTHER seem to work fine for me:

data test_storm_categories;
  retain fmtName "testStormFormat" type "n" ;
  input Start $ End $ Label $15.;
datalines;
low 63 No Category
64 82 Category 1
83 95 Category 2
96 112 Category 3
113 136 Category 4
137 high Category 5
other . Invalid number
;

proc format cntlIn = test_storm_categories fmtlib cntlout=demo;
  select testStormFormat;
run;

proc print data=demo;
  var fmtname type start end hlo label;
run;

Re: Proc Format: Using keywords in control input tables

Reeza — Wed, 28 Jul 2021 15:30:22 GMT

@kelbrosna So your initial code doesn't work because you have leading spaces in front of the word low, so SAS isn't recognizing it as low. If you trim the field it works fine. Leading spaces don't show in the table view which makes this one a bit harder to spot initially.

data test_storm_categories;
	length Start $8  End $8 Label $15;
	retain fmtName "testStormFormat" type "n";
	input Start $ End $ Label $;
	start = trim(start);
	infile datalines dlm = ",";
	datalines;
low,63,No Category
64,82,Category 1 
83,95,Category 2 	
96,112,Category 3 	
113,136,Category 4 
137,high,Category 5
	;
	run;
	
	proc format cntlin = test_storm_categories;
	run;

Thanks for the help with the issue resolution @Tom

Re: Proc Format: Using keywords in control input tables

Tom — Wed, 28 Jul 2021 15:56:48 GMT

So the interaction of all of the mistakes in that data step is what lead to the inclusion of leading spaces into the values.

1) Not starting the DATALINES line in column 1.

2) Not starting the actual data lines in column 1.

3) And finally not using the DSD option when using a delimiter other than space.

Re: Proc Format: Using keywords in control input tables

kelbrosna — Wed, 28 Jul 2021 19:11:13 GMT

@Tom and @Reeza,

Is there a way to tell SAS to trim leading and trailing blanks within datalines? Also, I originally switched to comma-delimited columns because I wasn't sure how to include a space in a column value when spaces are delimiters. Is there some kind of escape character in SAS (to tell SAS to read a space as a literal value, not a delimiter)?

Thanks,

kelbrosna

Re: Proc Format: Using keywords in control input tables

Tom — Wed, 28 Jul 2021 19:34:20 GMT

@kelbrosna wrote:

@Tom and @Reeza,

Is there a way to tell SAS to trim leading and trailing blanks within datalines? Also, I originally switched to comma-delimited columns because I wasn't sure how to include a space in a column value when spaces are delimiters. Is there some kind of escape character in SAS (to tell SAS to read a space as a literal value, not a delimiter)?

Thanks,

kelbrosna

Read the manual pages on INPUT statement. In particular check out the LIST MODE input method, which is what your code was using.

In list mode SAS reads variable length "words" from a line. The default delimiter is a space and multiple spaces are treated the same as one. This is great when the spaces have been added just to make the columns look neat on your punch card (or on your screen when using a fixed space font). You can use the & modify to tell SAS that it should require two or more consecutive spaces to mark the end of a "word". So use by adding that extra space between the variable values you can allow for embedded single spaces. That would work well for your example.

data test;
  length start end $8 label $15;
  input Start End Label & ;
datalines;
low      0 Negative value
    0   63 No Category
   64   82 Category 1
   83   95 Category 2
   96  112 Category 3
  113  136 Category 4
  137 high Category 5
other    . Invalid number
;

The other thing to notice is that last line of data. Since there is no value for END a period is used to represent a missing value. The regular $ informat (what SAS users be default for character variables) will convert the period to an empty (all spaces) value.

The DSD option allows SAS to use a delimited text line instead. Each value is separated from the other values by the delimiter. Two delimiters next to each other indicate a missing value. If you need to include the delimiter in the value then enclose the value in quotes. If you need to include a quote in the value then enclose the value in quotes and double up the quotes in the value. (Note that SAS will also allow you to use either double or single quotes on the outside although most consumers of such files only allow double quote characters.)

data test;
  length start end $8 label $15;
  infile datalines dsd truncover ;
  input Start End Label  ;
datalines;
0,1,"Zero,One"
other,,Unknown
;

Re: Proc Format: Using keywords in control input tables

Reeza — Wed, 28 Jul 2021 19:49:26 GMT

IME It is very rare to read anything from datalines except in tutorials/examples. For control tables, we use either SharePoint Lists or controlled forms and you read from a DB, or sometimes CSV files but very rarely datalines/cards are used.