Understanding of informat vs format

[ChrisWoo]

proc sql;
create table A (Name varchar(20), DOB num informat = best8. format = DDMMYYN8.);
insert into A
	values ("Johnson", 13031997);

Hi guys, im trying to understand the difference between informat and format.

Why with the above code, i will get result as below?

Name = Johnson

DOB = ********

How should I rewrite the code, so I can tell sas  to read that DOB as a Date as ddmmyyn8. and output as date. ?

I've tried multiple ways, but still get an output of DOB as ********.

Note:

The source received is 13031997 instead of "13Mar1997"d

[PaigeMiller]

My language here may not be precisely correct, but you can understand it this way:

Formats change how the information is displayed to humans. 

Informats change how the information is understood by SAS.

In other words, formats can translate between how SAS understands information and how humans understand information.

Example: A date in SAS is saved as the integer 21915. This is meaningless to humans, however by applying the DATE9. format, humans will see this exact same information as 01JAN2020. The format has translated the information from SAS to something a human understands.

Example (continued): Humans create a file of data, and one piece of data within that file is 01JAN2020. By applying informat DATE9., SAS now understands this as 21915, which it can use properly. The informat has translated the information from human language to SAS.

There are certainly other uses of formats and informats that don't involve dates, but they all involve changing the information from one appearance to another.

[ChrisWoo]

proc sql;
create table A (Name varchar(20), DOB num informat = DDMMYYN8.);
insert into A
	values ("Johnson", 01012020);

But in this case if i change the informat = ddmmyyn8.

SAS should be able to undertand that 01012020 is informat ddmmyyn8. and output as 21915. right?

[Tom · Accepted solution]

---

@ChrisWoo wrote:

proc sql;
create table A (Name varchar(20), DOB num informat = DDMMYYN8.);
insert into A
	values ("Johnson", 01012020);

But in this case if i change the informat = ddmmyyn8.

SAS should be able to undertand that 01012020 is informat ddmmyyn8. and output as 21915. right?

---

There is no informat named DDMMYYN.  There is a FORMAT with that name.  But the name of the INFORMAT that will recognize that string is DDMMYY.  It will recognize strings with or without delimiters between the components of the date.

But the VALUES statement does not use INFORMATs.  It just stores values.

If you want to read text into values use an INPUT statement or an INPUT() function call.  

Your program is more easily done using normal SAS code instead of SQL code. Especially for a lot of observations.

data A ;
  length Name $20 DOB 8;
  format dob date9.;
  informat dob ddmmyy.;
  input name dob  ;
datalines;
Johnson  01012020
Smith    12122021
;

PS Displaying dates in either DMY order or MDY order will confuse 50% of your audience.  Use either DATE or YYMMDD format to display dates if you want to avoid confusion.

[ChrisWoo]

got it. Thx

[Tom]

Formats convert values into text.  Informats convert text into values.

Numeric formats convert numbers into text.  Character formats convert text into other text.

Numeric informats convert text into numbers.  Character informats convert text into other text.

You use FORMATs with the PUT statement and the functions PUT (and PUTN and PUTC).  You attach formats to variables using the FORMAT statement (or the FORMAT= option in SQL).

You use INFORMATs with the INPUT statement and the functions INPUT (and INPUTN and INPUTC).  You attach informats to variables using the INFORMAT statement (or the INFORMAT= option in SQL).

In your example remove the INFORMAT= option as you are creating a dataset directly, not reading from text, so attaching an INFORMAT to the variable does nothing useful.  If you did want to attach an informat to that variable then it probably would be better to use one that would recognize the text that the format you are attaching to the variable will generate.

If you have text with the string '13031997'  and you want to convert that text into the data '03MAR1997'd then use the INPUT() (or INPUTN()) function with the DDMMYY informat.

In a VALUES() statement into SQL you need to put an actual DATE value.  So either use a date literal like '03MAR1997'd.   Or the actual number that is used to represent that date, which is 13586 since that is how many days after '01JAN1960'd it is.

insert into A values ("Johnson", '03MAR1997'd);
insert into A values ("Johnson", 13586);

PS  BEST is the name of a FORMAT.  The name derives comes from the fact it will attempt to convert the number in the best way that will fit into the width given.  It makes no sense to name an INFORMAT as BEST.  There is only one way to store a number, so there is no best solution for how to store the number.  If you accidentally use BEST as the name of an informat, as in your example program, then SAS will just use the normal numeric informat instead.