Convert character variable in unknown date/datetime format to numeric date

Question

I have about 30 datasets with a approx 20 date fields in each where the variable type varies between character and numeric as well as between date and datetime formats i.e. the following cases ...

a.) numeric - date b.) numeric - datetime c.) character - date d.) character - datetime

I want to convert each individual field to numeric and in date9. format. I tried to write the macro below which does not yield the correct results when the variable is a character string. What is going wrong? ... sas does not seem to interpret the input statement correctly if the character variable dateformat is not correctly specified.

%macro converttodate(inlib,indata,outlib,outdata,invar,outvar);

ods listing close;
ods output dataset.variables = work.formats;
proc contents data = &inlib..&indata.;
run;
data work.formats;
    set work.formats;
    where lowcase(compress(variable)) = lowcase(compress("&invar."));
run;
data _NULL_;    
    set work.formats;
    call symput('dtype',compress(lowcase(type)));
    call symput('dformat',compress(lowcase(format)));
    call symput('dlen',compress(put(len,8.)));
run;
%PUT INVAR  = ** &invar. ** OUTVAR = ** &outvar. **;
%PUT TYPE   = ** &dtype. **;
%PUT FORMAT = ** &dformat. **;
%PUT LENGTH = ** &dlen. **;

%if &dtype. = num %then %do;

    data &outlib..&outdata.;
        length tmp_put $50; 
        set &inlib..&indata.;
        format &outvar. date9.;
        tmp_put = compress(put(&invar.,&dformat.));
        if index(tmp_put,':') > 0 then &outvar. = datepart(&invar.); 
        else &outvar. = &invar.;
        drop tmp_put;
    run;

%end;
%else %do;

    data &outlib..&outdata.;
        set &inlib..&indata.;
        format &outvar. date9.;
        if index(&invar.,':') > 0 then &outvar. = datepart(input(&invar.,datetime.)); 
        else &outvar. = input(&invar.,date.);
    run;

%end;

%mend;

E.g.

data work.test;
    format x1 date9. y1 datetime30.6;
    x1 = mdy(10,16,1922);
    x2 = put(x1,date9.);
    y1 = 100000;
    y2 = put(y1,datetime30.6);
run;


%converttodate(
    inlib   = work,
    indata  = test,
    outlib  = work,
    outdata = test,
    invar   = x2,
    outvar  = x2_out);

Answer 1

This line in your macro is causing the unexpected behaviour:

else &outvar. = input(&invar.,date.);

The default length for date. is 7, so it reads the date as 16OCT19, i.e. year 2019

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_intervals_sect008.htm

If you change it to date9 it will work fine. If you want to change your entire macro you can look at the inputn function where the second parameter to the function can be dynamically determined by the length of the input data.

data &outlib..&outdata.;
    set &inlib..&indata.;
    format &outvar. date9.;

    in_format=catt("date", &dlen.);

    if index(&invar.,':') > 0 then &outvar. = datepart(input(&invar.,datetime.)); 

    else &outvar. = inputn(&invar.,in_format);

run;

Answer 2

If i use the correct length of the string in the input statement the output variable seem to become correct. I adjusted the code after %else %do; as below

%else %do;

    data _NULL_;
        set &inlib..&indata.;
        if length(compress(&invar.)) > 1 then do;
            call symput('dlen2',compress(put(length(compress(&invar.)),8.)));
            call symput('colon',compress(put((index(&invar.,':') > 0),8.)));
            stop;
        end;
        else do;
            call symput('dlen2','0');
        end;
    run;
    %if &dlen2. ^= 0 %then %do;
        data &outlib..&outdata.;
            set &inlib..&indata.;
            format &outvar. date9.;
            %if &colon. = 1 %then %do;
                &outvar. = datepart(input(&invar.,datetime&dlen2..)); 
            %end;
            %else %do;
                &outvar. = input(&invar.,date&dlen2..);
            %end;
        run;
    %end;
    %else %do;
        %PUT NO VALID OBSERVATIONS;
    %end;

%end;