topic Re: How to add a date condition while doing infile? in SAS Programming

How to add a date condition while doing infile?

Aashi07 — Wed, 24 Jul 2024 09:47:36 GMT

For task: If in the date columns there is 01/01/1900, replace with 01/01/0001,

I tried the following code:

data work.CGDM_IMP_PPLC;
              infile "&chemin_CGDM./W6_Extraction_CGDM_PPLC.csv" 
              delimiter='|' firstobs=2 lrecl=32767 missover dsd termstr=lf encoding='utf-8';
  
          format 
        DATE_DONNEES DDMMYY10. 
        DATE_ENTREE_ENTREPRISE DDMMYY10.
        DATE_SORTIE_ENTREPRISE DDMMYY10.
        DATE_ENTREE_ETABL DDMMYY10.
        DATE_SORTIE_ETABL DDMMYY10.
        IND_VIP 1.
        IND_SALARIE_DCD 1.
              ;
              input
       SOURCE
        DATE_DONNEES :YYMMDD10.
       DATE_ENTREE_ENTREPRISE :YYMMDD10.
        DATE_SORTIE_ENTREPRISE :YYMMDD10.
        DATE_ENTREE_ETABL :YYMMDD10.
        DATE_SORTIE_ETABL :YYMMDD10.
        IND_VIP
        IND_SALARIE_DCD

              ;
     if DATE_SORTIE_ENTREPRISE = '01/01/1900'd
           then DATE_SORTIE_ENTREPRISE = '01/01/0001'd;

run;

But I am obtaining this error:

Re: How to add a date condition while doing infile?

PaigeMiller — Wed, 24 Jul 2024 10:33:09 GMT

In an IF statement, you MUST use the date literal

if DATE_SORTIE_ENTREPRISE = '01JAN1900'd ...

to search for 01/01/1900.

However, I do not believe it is possible to assign a date of 01/01/0001, and so I think your task is impossible. Why do you want 01/01/0001 anyway? Why not just assign a missing value?

Re: How to add a date condition while doing infile?

ballardw — Wed, 24 Jul 2024 15:18:11 GMT

Currently the earliest date that SAS will use is 1 Jan 1582 and relates to when certain countries adopted the Gregorian calendar.

Custom informat and formats can approximate the behavior requested: (Note to @Aashi07 if your other dates are in Day Month Year order use DDMMYY10. below)

proc format library=work;
  invalue  mydates
  '01/01/1900'=.M
  other = [mmddyy10.]
  ;
  value mydates
  .M = '01/01/0001'
  other=[mmddyy10.]
  ;
run;

data example;
   input date mydates.;
   format date mydates.;
datalines;
01/01/1900
12/31/2021
02/01/2023
;

Assign a special missing, like the .M and use a custom format to display the special missing with desired appearance when needed. The missing values will not be included in any calculations unless using an option that specifies missing values will be used.

Re: How to add a date condition while doing infile?

Tom — Wed, 24 Jul 2024 15:30:03 GMT

Date literals require a string that the DATE informat can convert to a date.

But you cannot have a date before 1582. So if the CSV file use strings like 0001/01/01 they will be read by the YYMMDD informat as missing.

So it sounds like you just need to change the IF statement to assign a missing value instead.

if DATE_SORTIE_ENTREPRISE = '01JAN1900'd
  then DATE_SORTIE_ENTREPRISE = .
;

Or you could assign one of the 27 other special missing values instead.