How to detect date input between wrong format and empty input

[sarahzhou]

Hi Instructures,

The code below are insensitive whether the input to the DOB has the wrong formate or empty. I want to create two macros: &EmptyDT., &WrongDT. for later use to trigger error messages. How can I do that?

%let input_string =%nrbquote([{"MEMBERID": "A123456", "DOB": "2022-03-02"}]);

data json_text;
json_text="&input_String.";
run;

data Parse_JSON (drop=json_text drop=regex01 drop=regex02);
set WORK.json_text;
	retain 
		regex01
		regex02
;
  	if _N_ = 1 then do; 
		regex01=prxparse("/(.+?)MEMBERID(.+?)DOB/"); 
		regex02=prxparse("/(.+?)DOB(.+?)\}/"); 
	end;

	if prxmatch(regex01, json_text) then do;
		MEMBERID=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex01, 2, json_text)),":",""),",",""),'"',""));
    end;
	if prxmatch(regex02, json_text) then do;
		DOB=input(strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',"")),YYMMDD10.);
    end;
run;


proc sql noprint;
	/*Assign Macro vars to Params for ERRmsg*/
	select compress("'"||%nrquote(MEMBERID)||"'") into: MEMBERID from Parse_JSON;	
	select 
		case when MEMBERID in ("",".") then 1 
		else 0 end into: inv_MEMBERID_ind from Parse_JSON;

	select DOB as DoB_ format=yymmddd10. informat=yymmddd10. into: DOB from Parse_JSON;

	select 
		case when DOB ne . then 1
		else 0 end into: DOB_IND from Parse_JSON;
quit;

Kindly help!

Thanks 

[AMSAS]

Can you provide a simple example of what you have and what you want, as this doesn't make sense to me.

Why would you want to create macro variables, when I assume you will have multiple observations in work.json_text file
Are the macro variables just flags or counters (ie number of observations with empty date
How are you defining empty date vs wrong date? As far as SAS is concerned when you create your DOB variable it's either a valid SAS data value or missing

BTW When you create your DOB variable why are you using UPCASE and the informat YYMMDD10.?
The informat suggests you will always have numeric symbols in the value returned from the prxposn function, so it doesn't make sense to use UPACASE on those

prxposn(regex02, 2, json_text)

Sorry just confused on what you are attempting to do, and guessing others will be to

[Tom]

Not sure what your question is, but I would focus the REGEX on the part of the JSON you actually want to find.

Then dealing with the results will be easier.

data json_text;
  input json_text $80. ;
cards;
[{"MEMBERID": "A123456", "DOB": "2022-03-02"}]
[{"MEMBERID": "B789012"}]
[{"MEMBERID": "C345678", "DOB": "unknown"}]
;


data Parse_JSON ;
  length memberid $20 dob_raw $20 dob 8;
  format dob yymmdd10.;
  set WORK.json_text;
  if _N_ = 1 then do;
    retain regex01 regex02 ;
    drop regex01 regex02;
    regex01=prxparse('/(.+?)"MEMBERID":(.+?)[,\}\]]/');
    regex02=prxparse('/(.+?)"DOB":(.+?)[,\}\]]/');
  end;
  if prxmatch(regex01, json_text) then do;
    MEMBERID=dequote(strip(prxposn(regex01,2,json_text)));
  end;
  if prxmatch(regex02, json_text) then do;
    dob_raw=prxposn(regex02,2,json_text);
    DOB=input(dequote(strip(dob_raw)),yymmdd10.);
  end;
  else DOB=.M ;
run;

Results:

Obs    memberid      dob_raw              dob    json_text

 1     A123456     "2022-03-02"    2022-03-02    [{"MEMBERID": "A123456", "DOB": "2022-03-02"}]
 2     B789012                              M    [{"MEMBERID": "B789012"}]
 3     C345678     "unknown"                .    [{"MEMBERID": "C345678", "DOB": "unknown"}]

[sarahzhou]

Hi your results is correct. But how can I select the dob data in result in three different cases: 

1. Correct date (first result)

2. Empty date (2nd result)

3. Unkonw input date (3rd result)

proc sql noprint;
	/*Assign Macro vars to Params for ERRmsg*/
	select compress("'"||%nrquote(MEMBERID)||"'") into: MEMBERID from Parse_JSON;	
	select 
		case when MEMBERID in ("",".") then 1 
		else 0 end into: inv_MEMBERID_ind from Parse_JSON;

	select DOB as DoB_ format=yymmddd10. informat=yymmddd10. into: DOB from Parse_JSON;

	select 
		case when DOB ne . then 1
		else 0 end into: DOB_IND from Parse_JSON;
quit;

How do I write them into 

select 
		case when DOB ne . then 1
		else 0 end into: DOB_IND from Parse_JSON;

macros when 

If I check the %put &DOB_IND;

it should give

DOB_IND resolves to 0 When the date formate is empty

DOB_IND resolves to 1 When the date formate is keyed correctly

DOB_IND resolves to 2 When the date formate is keyed wrongly

Thank you!

[sarahzhou]

options symbolgen mlogic mlogicnest mprint;
%let input_string =%nrbquote([{"MEMBERID": "A123456      ", "DOB": "2022-03-03"}]);

data json_text;
json_text="&input_String.";
run;

data Parse_JSON (drop=json_text drop=regex01 drop=regex02);
set WORK.json_text;
	retain 
		regex01
		regex02
;
  	if _N_ = 1 then do; 
		regex01=prxparse("/(.+?)MEMBERID(.+?)DOB/"); 
		regex02=prxparse("/(.+?)DOB(.+?)\}/"); 
	end;

	if prxmatch(regex01, json_text) then do;
		MEMBERID=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex01, 2, json_text)),":",""),",",""),'"',""));
    end;
	if prxmatch(regex02, json_text) then do;
		DOB=input(strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',"")),YYMMDD10.);
    end;
run;


proc sql noprint;
	/*Assign Macro vars to Params for ERRmsg*/
	select compress("'"||%nrquote(MEMBERID)||"'") into: MEMBERID from Parse_JSON;	
	select 
		case when MEMBERID in ("",".") then 1 
		else 0 end into: inv_MEMBERID_ind from Parse_JSON;

	select DOB as DoB_ format=yymmddd10. informat=yymmddd10. into: DOB from Parse_JSON;

	select 
	select 
		case when DOB "Empty input" then 0
		case when DOB "Correct date formate" then 1
		case when DOB "Unknow formate" then 2
		into: DOB_IND from Parse_JSON;
quit;

Hi I am stucking in how to write them into the cases 

select case when DOB "Empty input" then 0 case when DOB "Correct date formate" then 1 case when DOB "Unknow formate" then 2 into: DOB_IND from Parse_JSON;

How can I do that?

[Tom]

You have to save the actual text from the JSON and not just the result of trying to turn it into a date value by using the INPUT() function.

See the DOB_RAW variable in my posted code.

Your SQL is really too scrambled to even make sense of.

If the goal is to create a macro variable for just ONE json text then just add the logic to calculate the value to the data step that is parsing the JSON.  You can use the CALL SYMPUTX() function in a data step to create a macro variable.  If the goal is to parse a whole series of JSON_TEXT strings from the source dataset (again see my example) and you still want to create macro variables you need to decide how to represent multiple observations of data into macro variables.  Do you want to generate a series of macro variables?  Perhaps with a numeric suffix?  

[sarahzhou]

The goal is to create marcro variables 

select case when DOB "Empty input" then 0 case when DOB "Correct date formate" then 1 case when DOB "Unknow formate" then 2 into: DOB_IND from Parse_JSON;

 the &DOB_IND.  is need when triggle the error messages.

I don't know how to select them into marco in these three cases.

[Tom]

You need to explain in more words what you want. Provide examples.

We can try to guess what you are talking about.

I worry that you are trying to jump into using the macro processor and you have not yet shown that you can write valid SAS code.  The macro processor is a tool to help you generate SAS code. So you need to understand how to generate SAS code.

It looks like you want to parse a JSON string.

What is the actual source of the JSON string?  Why did you start your program by putting the JSON string into a macro variable instead of into an example SAS dataset or a text file?

If you want to test if the text parsed from the JSON string empty or a valid date or any other things like that then you need to save the text you parsed out of the JSON string.  Your posted data step is not saving that text as it just passes through a series of function calls and ends up with a date value.  So there are only two choices of what can get stored into the date variable. Either a missing value or a valid date value.  There is no way to tell the difference between a missing value caused by an empty string or one caused by a non-sense string.  

So change your data step to save the string parse from the DOB tag in the JSON text into a CHARACTER variable.

If you want use SQL (why?) to create a macro variable you need to use an INTO clause in your SELECT statement.

If you want to use a CASE expression in your SQL select statement then use valid SQL syntax.

So if where able to create DOB as a character variable your SQL might look like:

proc sql noprint;
select case when (missing(DOB)) then 0 
            when (missing(input(DOB,?yymmdd10.)) then 2
            else 1 
       end
  into :DOB_IND 
  from Parse_JSON
;
quit;

[sarahzhou]

The input source is by a human to manually keyed the MEMBERID and DOB.

I have update the script, still have the error. T.T

options symbolgen mlogic mlogicnest mprint;
%let input_string =%nrbquote([{"MEMBERID": "A123456", "DOB": "2022-03-03"}]);

data json_text;
json_text="&input_String.";
run;

data Parse_JSON (drop=json_text drop=regex01 drop=regex02);
set WORK.json_text;
	retain 
		regex01
		regex02
;
  	if _N_ = 1 then do; 
		regex01=prxparse("/(.+?)MEMBERID(.+?)DOB/"); 
		regex02=prxparse("/(.+?)DOB(.+?)\}/"); 
	end;

	if prxmatch(regex01, json_text) then do;
		MEMBERID=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex01, 2, json_text)),":",""),",",""),'"',""));
    end;
	if prxmatch(regex02, json_text) then do;
		DOB=input(strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',"")),YYMMDD10.);
    end;
run;


proc sql noprint;
	/*Assign Macro vars to Params for ERRmsg*/
	select compress("'"||%nrquote(MEMBERID)||"'") into: MEMBERID from Parse_JSON;	
	select 
		case when MEMBERID in ("",".") then 1 
		else 0 end into: inv_MEMBERID_ind from Parse_JSON;

	select DOB  into: DOB from Parse_JSON;
    select case when DOB EQ "" then 0 
           case when DOB EQ input(DOB,?yymmdd10.) then 2
           else 1 end
    into :DOB_IND 
    from Parse_JSON;
quit;

%put &inv_MEMBERID_ind;
%put &DOB_IND;

The below is the log:

1                                                          The SAS System                              16:30 Thursday, March 3, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program 4';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         %macro HTML5AccessibleGraphSupported;
14             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
15         %mend;
16         FILENAME EGSR TEMP;
17         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18             STYLE=HTMLBlue
19             NOGTITLE
20             NOGFOOTNOTE
21             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to 
            "C:\Users\xxxxxxxx\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-23212-76e80bec\contents\SAS Temporary 
            Files\_TD25752_NCS-5CG12517WL_\Prc2/"
22             ENCODING=UTF8
23             options(rolap="on")
24         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25         
26         options symbolgen mlogic mlogicnest mprint;
27         %let input_string =%nrbquote([{"MEMBERID": "A123456      ", "DOB": "2022-03-03"}]);
28         


29         data json_text;
30         json_text="&input_String.";
SYMBOLGEN:  Macro variable INPUT_STRING resolves to [{"MEMBERID": "A123456      ", "DOB": "2022-03-03"}]
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
31         run;

NOTE: The data set WORK.JSON_TEXT has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      

32         
33         data Parse_JSON (drop=json_text drop=regex01 drop=regex02);
34         set WORK.json_text;
35         	retain
36         		regex01
37         		regex02
38         ;
39           	if _N_ = 1 then do;
40         		regex01=prxparse("/(.+?)MEMBERID(.+?)DOB/");
41         		regex02=prxparse("/(.+?)DOB(.+?)\}/");
42         	end;
43         
2                                                          The SAS System                              16:30 Thursday, March 3, 2022

44         	if prxmatch(regex01, json_text) then do;
45         		MEMBERID=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex01, 2, json_text)),":",""),",",""),'"',""));
46             end;
47         	if prxmatch(regex02, json_text) then do;
48         		DOB=input(strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',"")),YYMMDD10.);
49             end;
50         run;

NOTE: There were 1 observations read from the data set WORK.JSON_TEXT.
NOTE: The data set WORK.PARSE_JSON has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds
      

51         
52         
53         proc sql noprint;
54         	/*Assign Macro vars to Params for ERRmsg*/
55         	select compress("'"||%nrquote(MEMBERID)||"'") into: MEMBERID from Parse_JSON;
55       !                                                                               	
56         	select
57         		case when MEMBERID in ("",".") then 1
58         		else 0 end into: inv_MEMBERID_ind from Parse_JSON;
59         
60         	select DOB  into: DOB from Parse_JSON;
61             select case when DOB EQ "" then 0
62                    case when DOB EQ input(DOB,?yymmdd10.) then 2
                      ____
                      22
                      202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, ELSE, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, 
              |, ||, ~, ~=.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

63                    else 1 end
64             into :DOB_IND
65             from Parse_JSON;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
66         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
SYMBOLGEN:  Macro variable INV_MEMBERID_IND resolves to        0
67         
68         %put &inv_MEMBERID_ind;
0
69         %put &DOB_IND;
SYMBOLGEN:  Macro variable DOB_IND resolves to        1
1
70         
71         
72         
73         %LET _CLIENTTASKLABEL=;
3                                                          The SAS System                              16:30 Thursday, March 3, 2022

74         %LET _CLIENTPROCESSFLOWNAME=;
75         %LET _CLIENTPROJECTPATH=;
76         %LET _CLIENTPROJECTPATHHOST=;
77         %LET _CLIENTPROJECTNAME=;
78         %LET _SASPROGRAMFILE=;
79         %LET _SASPROGRAMFILEHOST=;
80         
81         ;*';*";*/;quit;run;
82         ODS _ALL_ CLOSE;
83         
84         
85         QUIT; RUN;
86         

I wish to get:

Macro variable DOB_IND resolves to 0 when DoB is empty
Macro variable DOB_IND resolves to 1 when DoB is correctly keyed
Macro variable DOB_IND resolves to 2 when DoB is wrongly keyed

How can I fix it?

Thank you

[ballardw]

Examples of actual "wrong input".

Example of what to actually do with the "wrong input".

[sarahzhou]

"wrong input" is when I keyed wrong input format for the DoB(Date of Birth) like suppose to key 2022-03-03 but I keyed in 2022-MAR-03 or ,20220303 , or 03032022, other than the format YYMMDD10.

[AMSAS]

How are the values keyed in manually?
Look at any Web page these days, you need to enter a date, it forces you to use a specific format. This is the way to go, much easier to enforce the correct value at the time of entry than attempt to fix/catch it later. 

There's a saying "Rubbish|Trash In = Rubbish|Trash out"