BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Moksha
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

7 REPLIES 7
Astounding
PROC Star

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
Diamond | Level 26

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.

2021-11-26 08_27_29-Reply to Message - SAS Support Communities — Mozilla Firefox.png

--
Paige Miller
Moksha
Pyrite | Level 9

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
Super User Tom
Super User

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
Super User

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
Super User Tom
Super User

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
Pyrite | Level 9

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1244 views
  • 4 likes
  • 5 in conversation