topic Re: Import pre-stored format table in New SAS User

Import pre-stored format table

malls — Sat, 21 Nov 2020 14:16:52 GMT

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.

Re: Import pre-stored format table

Kurt_Bremser — Sat, 21 Nov 2020 15:08:48 GMT

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.

Re: Import pre-stored format table

malls — Tue, 15 Dec 2020 19:06:33 GMT

Thank you for this quick reply.

Re: Import pre-stored format table

Kurt_Bremser — Sat, 21 Nov 2020 19:23:02 GMT

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.

Re: Import pre-stored format table

malls — Sat, 21 Nov 2020 20:09:48 GMT

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.

Re: Import pre-stored format table

Kurt_Bremser — Sat, 21 Nov 2020 20:13:06 GMT

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.

Re: Import pre-stored format table

Kurt_Bremser — Sat, 21 Nov 2020 20:16:15 GMT

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

Re: Import pre-stored format table

Tom — Sat, 21 Nov 2020 21:51:45 GMT

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

Re: Import pre-stored format table

malls — Tue, 15 Dec 2020 19:07:46 GMT

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 ...

Re: Import pre-stored format table

Tom — Sun, 22 Nov 2020 04:36:34 GMT

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;

Re: Import pre-stored format table

Kurt_Bremser — Sun, 22 Nov 2020 08:13:59 GMT

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

Re: Import pre-stored format table

malls — Tue, 15 Dec 2020 19:08:22 GMT

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,

Re: Import pre-stored format table

Kurt_Bremser — Sun, 22 Nov 2020 17:33:08 GMT

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).

Re: Import pre-stored format table

Tom — Mon, 23 Nov 2020 17:31:06 GMT

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.

Re: Import pre-stored format table

Kurt_Bremser — Mon, 23 Nov 2020 15:54:11 GMT

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?

Re: Import pre-stored format table

malls — Tue, 24 Nov 2020 14:46:09 GMT

My format table is .csv

Here is the code iused to import this table :

proc import datafile='E:\PUF AY 2017\PUF AY 2017\CSV\PUF_FORMATS.csv'
out=kmus.FORMATS dbms=csv replace;
run;

Further, i submitted these codes :

data formats;
set kmus.formats;
run;

DATA fmtDataset;
SET formats;
RENAME Value = Start;
run;

PROC FORMAT CNTLIN=fmtDataset type="C";
RUN;
data test; set dixsept;
format statedesignation statedesignation.;
run;

proc freq data=test;
table statedesignation;
run;

Sadly, i still get missing values.

Re: Import pre-stored format table

Tom — Tue, 24 Nov 2020 15:09:53 GMT

Don't use PROC IMPORT to read CSV files. It has to GUESS how to define the variables. Just write your own data step to read the CSV file and then you can control how the variables are defined.