proc format: with conditions

[Ronein]

Hello

I want to add format to ddate field in the following way:

IF  the date is 01/01/1900 then it would show " Date not exist"

and in other cases will show the date

So;

01JAN1900  --will show  Date not exist
01JAN1900  --will show  Date not exist
15DEC2023  --will show 15/12/2023
16JAN2024--will show 16/01/2024
07SEP2021--will show 07/09/2021

what is the way to do it please?

data have;
format ddate ddmmyy10.;
input ddate : date9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

[Cynthia_sas]

Hi:

  You just need a nested format as shown in the doc https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1upn25lbfo6mkn1wncu4dyh9q91.htm#p1qm... and in this example:

Basically a nested format is where you define a format that references another format, which is your use case.

Cynthia

[Ronein]

I have tried but it didnt work

data have;
format ddate ddmmyy10.;
input ddate : date9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

proc format;
invalue mydate
'01JAN1900'='n/a';
other=[date9.];
run;


data want;
set have;
format ddate mydate.;
Run;

[Quentin]

---

@Ronein wrote:

I have tried but it didnt work

data have;
format ddate ddmmyy10.;
input ddate : date9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

proc format;
invalue mydate
'01JAN1900'='n/a';
other=[date9.];
run;


data want;
set have;
format ddate mydate.;
Run;

---

For Cynthia's format approach, you need a VALUE statement, not INVALUE.  You're also missing the d after the date literal '01JAN1900' and you have an extra semicolon.  When you run that code, you should see lots of helpful error messages in the log.

Try:

proc format;
value mydate
'01JAN1900'd='n/a'
other=[date9.];
run;

[Stu_SAS]

This is a great solution, @Cynthia_sas! One alternative option is to use a picture format which is a powerful way to have a high level of control on how you'd like to display dates and datetimes:

proc format;
    picture ddmmyydne
        '01JAN1900'd = 'Date not exist'
        other        = '%0d/%m/%Y' (datatype=date)
    ;
run;

Test:

proc print data=have;
    format ddate ddmmyydne.;
run;

[mkeintz]

Why not convert all instances of 01JAN1900 to missing values?  Either by recoding:

if ddate='01jan1900'd then ddate=.;

or through a customized informat:

proc format;
  invalue mydat
      '01JAN1900'=.
      other=[date9.];
run;

data have;
  format ddate ddmmyy10.;
  input ddate :mydat.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
Run;

This will allow more appropriate treatment of dates and date intervals in subsequent DATA steps and PROCs.

[Ronein]

It is not working,

I want to apply it on have data set but do it when create want data set  (I dont want to do it in the data step with cards statement because in real word I dont create the data set with dates and only get it as external data set)

data have;
format ddate ddmmyy10.;
input ddate : date9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;


proc format;
invalue mydate
'01JAN1900'=.
other=[date9.];
run;


data want;
set have;
informat ddate mydate.;
Run;

[Stu_SAS]

You're close! Let's look at this from a few different angles. The way you originally described your dataset indicates that your dates come in as SAS dates already. If this is the case, then you only need to use a single format statement:

proc format;
    value mydate
        '01JAN1900'd='Date not exist'
        other=[ddmmyy10.];
run;

data want;
    set have;
    format ddate mydate.;
Run;

If your dates are strings, then you need to first convert the string date to a SAS date and apply your format:

data have;
input ddate$9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

proc format;
    value mydate
        '01JAN1900'd='Date not exist'
        other=[ddmmyy10.];
run;

data want;
    set have;    
    format ddate2 mydate.;
    ddate2 = input(ddate, date9.);
run;

If you want the data to always be a string without needing to use if-then-else, use the PUT function:

data have;
input ddate$9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

proc format;
    value mydate
        '01JAN1900'd='Date not exist'
        other=[ddmmyy10.];
run;

data want;
    length ddate $14.;
    set have;    
    ddate = put(input(ddate, date9.), mydate.);
run;

[Ronein]

Thanks
Indeed dates in my data set are sas dates. So as I see I has 2 mistakes. 1- forgot 'd in the format definition 2- used information instead of format statement

[mkeintz]

---

@Ronein wrote:

It is not working,

I want to apply it on have data set but do it when create want data set  (I dont want to do it in the data step with cards statement because in real word I dont create the data set with dates and only get it as external data set)

data have;
format ddate ddmmyy10.;
input ddate : date9.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;


proc format;
invalue mydate
'01JAN1900'=.
other=[date9.];
run;


data want;
set have;
informat ddate mydate.;
Run;

---

The INFORMAT is useful only for an INPUT statement or function.  You ignored my first alternative, meant for modify data that has already been input:

if ddate='01jan1900'd then ddate=.;

which would allow all subsequent uses of the data to recognize the date as a missing value.  (useful for reporting date ranges for your data, or for generating derived dates, but only from non-missing DDATEs).

[Cynthia_sas]

Hi:

  I see what you want to do now, but I would never do it that way. I used to do work for attorneys for court cases and we would never be able to change the original data as you show. We could alter the format for display on reports or we could make a new variable based on the original variable, but we were not allowed to alter any of our original data -- we always had to be able to go back to the original data and show where it had been transformed. So if I still worked for attorneys, I'd have to leave DDATE alone and then either make a new character variable (if the string 'Date Not Exist' absolutely needed to be in the WANT dataset or I'd just make a new numeric variable and apply the format for display purposes:

In this example, the WANT program uses the SAME format that I created previously:

data want;
  length change_date_value_char $15;
  set have;
  change_date_value_char = put(ddate,dtfmt.);
  change_date_value_num = ddate;
  format change_date_value_num dtfmt. ddate date9.;
  label ddate='Original Data Value'
        change_date_value_char = 'Character Date Value'
		change_date_value_num = 'New Date with DTFMT applied';
run;
proc print data=want label;
  var ddate change_date_value_char change_date_value_num;
run;
 

Cynthia

[ballardw]

My likely solution to this would look more like:

proc format;
invalue mydate
'01JAN1900'=.N
other=[date9.];
value mydate
.N='Date not exist'
other=[ddmmyy10.];
run;


data have;
format ddate ddmmyy10.;
input ddate : mydate.;
format ddate mydate.;
cards;
01JAN1900
01JAN1900
15DEC2023
16JAN2024
07SEP2021
;
Run;

This use of a special missing from the custom informat would differentiate the result from any other malformed or missing "date" input. The custom format would also let you know that other missing "dates" were not the special original value and could be handled differently when needed.

Special missing such as the .N (or any of the other .letter or ._) can be tested in comparisons such as :

if ddate = .N then <do something that might differ from simple missing>;

but the values are still missing for purposes like calculations.