Proc Format: Using keywords in control input tables

[Reeza · Accepted solution]

@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 

[Tom]

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.

[kelbrosna]

@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

[Tom]

---

@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
;

[Reeza]

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.