Check the input of date is a correct input format of the date

[sarahzhou]

Hi Instructures,

I have the below code, which I need to check the DOB is a correct input format; the DOB can have three different cases: empty input, correct input date format, and the wrong input date format according to the input_string [{"MEMBERID": "A123456", "DOB": "2022-FEB-03"}]

%let input_string =%nrbquote([{"MEMBERID": "A123456", "DOB": "2022-FEB-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.);*/
		DOB=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',""));

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 compress("'"||%nrquote(DOB)||"'") into: DOB from Parse_JSON;
	select 
		case when DOB in ("",".") then 0 /*when date is empty*/
		case when DOB=input(dequote(strip(DOB)),yymmdd10.) then 1 /*when date formate is correct*/
		else 2 end into: DOB_IND from Parse_JSON;/*when date formate is wrong format*/
quit;

%put &DOB_IND;

in this example should expected that log will show

SYMBOLGEN: Macro variable DOB_IND resolves to 2

The source of input is key manually so it need to able to caught the wrong format of the input.

Kindly help!

[AMSAS]

Simplify the question, you have a lot of code here, that really you don't need to have to explain the issue.

I think based on your previous post and this one, that this is what you want to do:

data have ;
	infile cards ;
	input dates : $12. wantedResult ;
	put dates= @25 wantedResult= ;

cards ;
2022-Feb-03 2
2022-02-03 1
. 0
2020-02-41 2
;
run ;

Here's what that generates 

429  data have ;
430      infile cards ;
431      input dates : $12. wantedResult ;
432      put dates= @25 wantedResult= ;
433
434  cards ;

dates=2022-Feb-03       wantedResult=2
dates=2022-02-03        wantedResult=1
dates=                  wantedResult=0
dates=2020-02-41        wantedResult=2
NOTE: The data set WORK.HAVE has 4 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


439  ;
440  run ;

You want to know what code logic you need to get wantedResult  based on the value of dates (yes I know I hard coded it, but this is to explain the question, so we understand what you are trying to do).

I'm going to say the same thing I said in the prior post:

How are the values keyed in manually?
Look at any Web page these days, if 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"

[sarahzhou]

People might mistakly key the date in wrong format, my goal is to triggle the error message:

%if &CountMemberID.>1 %then %do;
	errmsg="Multiple member found, please enter the DOB for the member.
	run;
	%goto SkipTB;
%end;

%if &CountMemberID.>1 and &DOB_ind. = 2 %then %do;
	errmsg="Invalid Input DOB formate, please enter DOB in YYYY-MM-DD format
	run;
	%goto SkipTB;
%end;

and when &CountMemberID.=1 the script is able to located the member and return the shopping history for that member.( In reality the MemberID should not have the duplication, but some member are quite long time ago, the numbering system for the member ID might have the duplicate members)

That's why I need the macro &DOB_ind.

How can I rectify my code T.T

[Tom · Accepted solution]

You really do need to explain why your input appears to be JSON text in a macro variable. Why isn't the input just two macro variables with the values of MEMBERID and DOB?  Forcing the use of JSON is just complicating the issue.

It looks like your data step is now successfully converting the JSON text into two character variables.

Not clear why you keep trying to code SQL steps, you do not seem to understand the syntax.  You cannot have a SELECT without a FROM.

It is much easier to use normal SAS code.  You could do it in the same data step that is parsing the JSON.

Here is how to do starting with the output of the other data step as input.

data _null_;
  set parse_json;
  call symputx('memberid',quote(trim(memberid),"'"));
  call symputx('dob',dob);
  if strip(dob) in (' ' '.') then dob_ind=0;
  else if not missing(input(dob,??yymmdd10.)) then dob_ind=1;
  else dob_ind=2;
  call symputx('dob_ind',dob_ind);
run;

%put &=memberid;
%put &=dob;
%put &=dob_ind;

Results:

809   %put &=memberid;
MEMBERID='A123456'
810   %put &=dob;
DOB=2022-FEB-03
811   %put &=dob_ind;
DOB_IND=2

[sarahzhou]

Thank you! It worked.

[Sajid01]

Hello
This can also be solved using libname json as shown here (https://communities.sas.com/t5/SAS-Programming/Return-a-input-string-as-a-JSON-table/td-p/788653 )
Following this approach the code will be

%let input_string =%nrbquote([{"MEMBERID": "A123456", "DOB": "2022-FEB-03"}]);
filename j temp;
data _null_;
file j;
put "&input_string";
run;
libname data json fileref=j;
data output;
 set data.root;
  if not missing(input(DOB,??yymmdd10.)) then DOB_IND=1;
  else DOB_IND=2;
run;
proc print data=output;
run;

The output will be like as follows.

[sarahzhou]

Hi,

The same code when I added one more member, it has a wired bug ...

The code:

%let input_string =%nrbquote([{"MEMBERID1": "A12345690", "DOB1": "unknown", "MEMBERID2": "A12345677", "DOB2": "2022-01-03"}]);

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

data Parse_JSON_OID (drop=json_text drop=regex01 drop=regex02 drop=regex03 drop=regex04);
set WORK.json_text;
	retain 
        regex01
		regex02
        regex03
		regex04
;
  	if _N_ = 1 then do; 
		regex01=prxparse("/(.+?)MEMBERID1(.+?)DOB1/"); 
		regex02=prxparse("/(.+?)DOB1(.+?)\}/"); 
        regex03=prxparse("/(.+?)MEMBERID2(.+?)DOB1/"); 
		regex04=prxparse("/(.+?)DOB2(.+?)\}/"); 
	end;
	if prxmatch(regex01, json_text) then do;
		MEMBERID1=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex01, 2, json_text)),":",""),",",""),'"',""));
    end;

	if prxmatch(regex02, json_text) then do;
		DOB1=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex02, 2, json_text)),":",""),",",""),'"',""));
    end;

	if prxmatch(regex03, json_text) then do;
		MEMBERID2=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex03, 2, json_text)),":",""),",",""),'"',""));
    end;
	if prxmatch(regex04, json_text) then do;
		DOB2=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex04, 2, json_text)),":",""),",",""),'"',""));
    end;run;



data _null_;
  set Parse_JSON_OID;
  call symputx('MEMBERID1',quote(trim(MEMBERID1),"'"));
  call symputx('DOB1',DOB1);
  if strip(DOB1) in (' ' '.') then dob1_ind=0;
  else if not missing(input(DOB1,??yymmdd10.)) then dob1_ind=1;
  else dob1_ind=2;
  call symputx('dob1_ind',dob1_ind);

  call symputx('MEMBERID2',quote(trim(MEMBERID2),"'"));
  call symputx('DOB2',DOB2);
  if strip(DOB2) in (' ' '.') then dob2_ind=0;
  else if not missing(input(DOB2,??yymmdd10.)) then dob2_ind=1;
  else dob2_ind=2;
  call symputx('dob2_ind',dob2_ind);
run;

%put &=MEMBERID1;
%put &=MEMBERID2;
%put &=dob1;
%put &=dob2;
%put &=dob1_ind;
%put &=dob2_ind;
%put &dob1_ind;

and the DOB1_IND is wrong in this case.

How can I resolve both DOB1_IND and DOB2_IND correctly as before?

Thank you.

[Sajid01]

Hello @sarahzhou 
There are many approaches to solve your issue.
I would use the following. I have modified your input string to a json format.
Adding any number of users would not create an issue.
Have a look at the code below:

%let input_string =%nrbquote([{"MEMBERID": "A12345690", "DOB": "unknown"}, {"MEMBERID": "A12345677", "DOB": "2022-01-03"}]);
filename j temp;

data _null_;
file j;
put "&input_string";
run;

libname data json fileref=j;
data output;
 set data.root; 
  if not missing(input(DOB,??yymmdd10.)) then DOB_IND=1;
  else DOB_IND=2;
run;

proc print data=output;
var MEMBERID DOB DO_IND;
run;

Your output will come out as follows. For memberid A12345690 the date is not in the date format or essentiall it is missing so DOB_IND=2. For the other memberid, THE DOB is available and it is 1.

[sarahzhou]

Wow...Thank you! It worked. 🙂