Import pre-stored format table

[malls]

Hi all,

I recently received a dataset with formats separated in another table. 

I would really like any help on how to import/link these formats to my dataset.

Thank you.

[Kurt_Bremser]

These tables should be in the CNTLIN layout as found in the documentation of PROC FORMAT, Input Control Dataset.

If yes, just run PROC FORMAT with the CNTLIN option.

If no, please show us an example for those tables, so we can suggest a way to turn them into formats.

[malls]

Thank you for this quick reply.

[Kurt_Bremser]

The minimum of columns you need in a CNTLIN datasets are these:

FMTNAME

TYPE N or C

START value to be formatted

LABEL formatted value

and if you need to mark a label for "other"

HLO

Length is not important (will be automatically assumed from the maximum LABEL length)

So, VALUE needs to be renamed to START, and a TYPE column added; the value of this can be deducted from the contents of START, and the types of the variable where the format is used.

[malls]

Awesome. 

The CNTLIN worked.

However, once i apply it to the data, i still got these values (I, N, O) to the variable "statedesignation" instead of values (I, II, III, IV etc...). I sure missed something.

[Kurt_Bremser]

Run a PROC CONTENTS on the dataset (that with the data, not the CNTLIN) to see which type the variable is and which format is assigned to it.

[Kurt_Bremser]

Your log says that all formats were created as informats (type = "I"). Make sure that the TYPE is correctly set for the later use.

[malls]

Proc contents says "STATEDESIGNATION" is a character variable with format $1. and Informat $1 (type="I") should be ok.

The format dataset says values are I, II, III, IV etc...

However, in my dataset, it looks like this : 

Hence my need to try applying the formats to get all hospital levels as I, II, III and so on. Maybe i'm wrong ...

[Tom]

The $ format does nothing to the values (other than possibly truncating them if the width is less than the length of the variable).  The informat attached to a variable is of no consequence once the variable values are already in the dataset.

So if STATEDESIGNATION is using the $1. format to display its values the what you will see in the report is actual values (since it looks like the length is also only 1).   Your report is showing values of 'I', 'N' and 'O' plus a lot of blank values which were treated as missing.  

If you have defined a format that converts those values in strings like 'I', 'II', 'III' then you need to make sure to use that format with the variable when you run the report.

So something like this:

proc format ;
value $decode 'I'='I' 'N'='II' 'O'='III' ;
run;
proc freq ;
  tables STATEDESIGNATION ;
  format STATEDESIGNATION  $decode. ;
run;

[Tom]

The TYPE is not required.  It will default to numeric format, unless the FTMNAME starts with a $ then it will define a character format.

[Kurt_Bremser]

---

@Tom wrote:

The TYPE is not required.  It will default to numeric format, unless the FTMNAME starts with a $ then it will define a character format.

---

You are of course right about that, but there is at least one format in there that expects a character variable (the bottom one in the screenshot), and no FMTNAME has a $ in it; so we need to set the TYPE for those to "C", or add the dollar sign in the name.
Anyway, there must be a TYPE already in the dataset, containing all I's , or the FORMAT procedure would not have created all those informats.

@malls Informats are only needed when reading data from external sources (text files), they do not influence how values are displayed. For that you need formats.

If you can't fix your issues with all the suggestions we already provided, we need to get an example for the "format" dataset that contains all columns and examples for their contents; this is best done by posting a data step with datalines. The macro linked in my footnotes can convert a dataset to such data step code.

Your PROC CONTENTS output reveals that no formats are assigned to the columns, you will need to do that yourself; a documentation linking formats to columns would come in handy. Setting the formats can be done with PROC DATASETS, so you do not need to rewrite the whole dataset.

[malls]

Here is the information provided in the user manual :

The format dataset looks like this :

And here is the data step i applied to the format data set :

DATA fmtDataset;
SET formats;
RETAIN fmtname "$fmtsatedesignation" type = "I";
RENAME Value = Start;
run;
PROC FORMAT CNTLIN=fmtDataset;
RUN;

proc contents data=kmus.trauma;
run;

This is an example of the proc contents result :

thank you,

[Kurt_Bremser]

Why did you use a TYPE of "I"? That causes all the formats to become informats and be useless.

Since all formats seem to be numeric anyway, no TYPE is needed (see @Tom ), you can remove the RETAIN statement completely (FMTNAME is already contained in the dataset).

[Tom]

Looks like you defined the variables in your dataset as character instead of numeric.  So use TYPE='C' in your CNTLIN dataset.

Also the handling of the missing values in VALUE column in the FORMAT dataset should be character.  From the way it is posing in the photograph you posted it appears that it is currently defined as numeric.  Did you make that dataset from a CSV file? If so make sure to read the field as character, not numeric.

[Kurt_Bremser]

In which format did you get/download the datasets? As .sas7bdat files, or as .csv files you then imported?

And if the latter, how did you import them?