What is wrong with the this marco code for imputing date and time

[Moksha]

Hi All,

      When run the following code for imputing the date and time, I am getting the following errors. Can anyone help me what is wrong with this? Also, I do not understand why prxid variable value is showing different values whenever I execute the call statement %splitIso8601dtc( inDat= ae, outDat= ksfe_2018, var= aestdtc ).

Can anyone help me to understand what is wrong with the code and how correct it?

Errors are:

ERROR: Invalid regular expression delimiter "p" in "prxid". Delimiter must be non-alphanumeric
and non-whitespace.
ERROR: The regular expression passed to the function PRXMATCH contains a syntax error.
WARNING: Argument 1 to function PRXMATCH referenced by the %SYSFUNC or %QSYSFUNC macro function
is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The
result of the operations have been set to a missing value.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: %sysfunc(prxmatch(prxid,&var))
ERROR: The macro SPLITISO8601DTC will stop executing.

Sample data set for testing the code:

DATA ae;

length aestdtc $ 20.;

aestdtc= '2017-03'; OUTPUT;

aestdtc= '2017-03-01T14'; OUTPUT;

aestdtc= '2017---15'; OUTPUT;

aestdtc= '2017-03--T07:30'; OUTPUT;

aestdtc= '2017----T07:30'; OUTPUT;

RUN;

proc print;run;

Macro code is follows:

%MACRO splitIso8601dtc( inDat= , outDat= , var= ) / DES='Macro to separate ISO 8601 datetime components';

DATA &outDat;

SET &inDat;

* create regular expression ID to separate is8601dt datetime components;

%let prxid= %sysfunc(prxparse('/^(\d{4})(-(\d{2}|-)(-(\d{2}|-)(T(\d{2}|-)(:(\d{2}|-)(:(\d{2}))?)?)?)?)?\s*$/'));

%put var is.............&var;

%put prxid is ...........&prxid;

%IF %sysfunc(prxmatch(prxid,&var)) %THEN %DO;

* separate datetime components;

&var._year= input(compress(%sysfunc(prxposn(prxid,1,&var),'-')), 4.);

&var._min_month= input(compress(%sysfunc(prxposn(prxid,3,&var),'-')), 2.);

&var._min_day= input(compress(%sysfunc(prxposn(prxid,5,&var),'-')), 2.);

&var._min_hour= input(compress(%sysfunc(prxposn(prxid,7,&var),'-')), 2.);

&var._min_minute= input(compress(%sysfunc(prxposn(prxid,9,&var),'-')), 2.);

&var._min_second= input(compress(%sysfunc(prxposn(prxid,11,&var),'-')), 2.);

* impute missing datetime components;

* impute month when missing;

%IF missing(&var._min_month) %THEN %DO;

&var._min_month= 1;

&var._max_month= 12;

%END;

%ELSE %DO;

&var._max_month= &var._min_month;

%END;

* impute day when missing;

%IF missing(&var._min_day) %THEN %DO;

&var._min_day= 1;

* Last day of the respective month and year;

&var._max_day= day(intnx('month',input(cats(put(&var._year,z4.),'-'

,put(&var._max_month,z2.),'-01')

,is8601da.)

,0

,'E'));

%END;

%ELSE %DO;

&var._max_day= &var._min_day;

%END;

* impute hour when missing;

%IF missing(&var._min_hour) %THEN %DO;

&var._min_hour= 0;

&var._max_hour= 23;

%END;

%ELSE %DO;

&var._max_hour= &var._min_hour;

%END;

* impute minute when missing;

%IF missing(&var._min_minute) %THEN %DO;

&var._min_minute= 0;

&var._max_minute= 59;

%END;

%ELSE %DO;

&var._max_minute= &var._min_minute;

%END;

* impute second when missing;

%IF missing(&var._min_second) %THEN %DO;

&var._min_second= 0;

&var._max_second= 59;

%END;

%ELSE %DO;

&var._max_second= &var._min_second;

%END;

* create the full date/time value;

FORMAT &var._min &var._max is8601dt.;

&var._min= dhms(mdy(&var._min_month, &var._min_day, &var._year)

,&var._min_hour, &var._min_minute, &var._min_second);

&var._max= dhms(mdy(&var._max_month, &var._max_day, &var._year)

,&var._max_hour, &var._max_minute, &var._max_second);

    %END;

%MEND splitIso8601dtc;

Calling the above marco:

%splitIso8601dtc( inDat= ae, outDat= ksfe_2018, var= aestdtc )

Thanks

[Astounding]

Too many pieces to this that I don't understand.  But the one I do understand is this. 

Macro language %IF %THEN statements do not execute for every observation in a SAS data set.  They merely generate DATA step code.  So this statement can't work as intended:

%IF missing(&var._min_day) %THEN %DO;

Even adding %SYSFUNC around the MISSING function won't help.  You would need to convert to DATA step IF THEN statements instead.

[PaigeMiller]

For debugging can eliminate the part of the macro after the PRXMATCH command, let's say eliminate code after the * impute missing datetime components; comment.

Then since you have errors in the log, show us the ENTIRE log for this abbreviated macro. Do not shows us only the errors. Do not pick and choose parts of the log to show us.

Since you have errors in a macro, you need run the macro debugging command

options mprint;

and then re-run the macro and show us the ENTIRE log. Please copy and paste the log into the window that appears when you click on the </> icon.

[Moksha]

Hi PaigeMiller, I have edited my earlier reply with the mprint option as after re-opening the SAS, I missed to run the code to create the dataset. Now, I have run it and here are the errors in the log with mprint:

options mprint;
96
97 %splitIso8601dtc( inDat= ae, outDat= ksfe_2018, var= aestdtc )
MPRINT(SPLITISO8601DTC): DATA ksfe_2018;
MPRINT(SPLITISO8601DTC): SET ae;
MPRINT(SPLITISO8601DTC): * create regular expression ID to separate is8601dt datetime
components;
var is.............aestdtc
prxid is ...........1
ERROR: Invalid regular expression delimiter "p" in "prxid". Delimiter must be non-alphanumeric
and non-whitespace.
ERROR: The regular expression passed to the function PRXMATCH contains a syntax error.
WARNING: Argument 1 to function PRXMATCH referenced by the %SYSFUNC or %QSYSFUNC macro function
is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The
result of the operations have been set to a missing value.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: %sysfunc(prxmatch(prxid,&var))
ERROR: The macro SPLITISO8601DTC will stop executing.

[Tom]

You are trying to use macro code to work with data.  That cannot work.  You use the macro processor to generate SAS code.  It is the job of the SAS code the macro generates to work with the data.

So this statement should not be using macro code:

%let prxid= %sysfunc(prxparse('/^(\d{4})(-(\d{2}|-)(-(\d{2}|-)(T(\d{2}|-)(:(\d{2}|-)(:(\d{2}))?)?)?)?)?\s*$/'));

And the reason it is getting an error is because you included quotes where they are not needed.  So those because part of the string you passed to PRXPARSE().  So it sees the single quote character as the first character so it uses that as the separator instead of the normal / character.

[Kurt_Bremser]

Step 1: get rid of all the macro code and create a DATA step which does it for a single variable in a single dataset.

Since you only want the dataset and variable names to be dynamic, only those need to be replaced by macro parameters. There is absolutely NO need to use %IF anywhere.

[Tom · Accepted solution]

DO NOT use MACRO code where you need SAS code.

%macro splitIso8601dtc
(inDat=    /* Input dataset name */
,outDat=   /* Output dataset name */
,var=      /* Input characer variable name */
) / DES='Macro to separate ISO 8601 datetime components';

%local pattern ;
%let pattern = /^(\d{4})(-(\d{2}|-)(-(\d{2}|-)(T(\d{2}|-)(:(\d{2}|-)(:(\d{2}))?)?)?)?)?\s*$/;
%put NOTE: &sysmacroname: &=indat &=outdat &=var &=pattern;

* create new dataset from existing dataset ;
DATA &outDat;
  SET &inDat;
 
* create regular expression ID to separate is8601dt datetime components;
  if _n_=1 then prxid= prxparse("&pattern");
  retain prxid ;
 
* separate datetime components;
  if prxmatch(prxid,&var) then do; 
    &var._year= input(prxposn(prxid,1,&var), ??4.);
    &var._min_month= input(prxposn(prxid,3,&var), ??2.);
    &var._min_day= input(prxposn(prxid,5,&var), ??2.);
    &var._min_hour= input(prxposn(prxid,7,&var), ??2.);
    &var._min_minute= input(prxposn(prxid,9,&var), ??2.);
    &var._min_second= input(prxposn(prxid,11,&var), ??2.);
 
* impute missing datetime components;
* impute month when missing;
    if missing(&var._min_month) then do;
      &var._min_month= 1;
      &var._max_month= 12;
    end;
    else do; 
      &var._max_month= &var._min_month;
    end;

* impute day when missing;
    if missing(&var._min_day) then do;
      &var._min_day= 1;
* Last day of the respective month and year;
      &var._max_day=day(intnx('month',mdy(&var._max_month,1,&var._year),0,'E'));
    end;
    else do;
      &var._max_day= &var._min_day;
    end;
 
* impute hour when missing;
    if missing(&var._min_hour) then do;
      &var._min_hour= 0;
      &var._max_hour= 23;
    end;
    else do;
      &var._max_hour= &var._min_hour;
    end;
 
* impute minute when missing;
    if missing(&var._min_minute) then do;
      &var._min_minute= 0;
      &var._max_minute= 59;
    end;
    else do;
      &var._max_minute= &var._min_minute;
    end;
 
* impute second when missing;
    IF missing(&var._min_second) then do;
      &var._min_second= 0;
      &var._max_second= 59;
    end;
    else do;
      &var._max_second= &var._min_second;
    end;
 
* create the full date/time value;
    &var._min= dhms(mdy(&var._min_month, &var._min_day, &var._year)
                   ,&var._min_hour, &var._min_minute, &var._min_second)
    ;
    &var._max= dhms(datepart(&var._min)
              ,&var._max_hour, &var._max_minute, &var._max_second)
    ;
  end;
  FORMAT &var._min &var._max is8601dt.;
run;

%MEND splitIso8601dtc;

* Sample data ;
data ae;
  length aestdtc $20;
  input aestdtc ;
datalines;
2017-03
2017-03-01T14
2017---15
2017-03--T07:30
2017----T07:30
;

 
* call the macro ;
options mprint;
%splitIso8601dtc( inDat= ae, outDat= ksfe_2018, var= aestdtc )

proc print;
run;

[Moksha]

Thank you very much Tom. It's working now. It helped me a lot.  Now, I understand what went wrong with the code. Once again, thank you very much.