Wrong text pattern caught in the input date format

[sarahzhou]

Hi,

I need to resolve both date of birth (DOB) for two members, MEMBERID1 and MEMBERID2. I need the DOB1 AND DOB2 can be caught eighter its empty input, correct date fomate input, or wrong date formate input. It works fines with only one member, and now has a bug.

%let input_string =%nrbquote([{"MEMBERID1": "A12345690", "DOB1": "2022-02-03", "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(.+?)DOB2/"); 
		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;

The DOB1 is catching a wrong string Macro variable DOB1 resolves to 2022-02-03 MEMBERID2 A12345677 DOB2 2022-01-03.

How can I fix this?

I want

DOB1 resolves to 2022-02-03, Macro variable DOB1_IND resolves to 1

DOB2 resolves to 2022-01-03, Macro variable DOB1_IND resolves to 1

in this input. If the BOB1 is empty then, it should give Macro variable DOB1_IND resolves to 0. according the if else conditiong in above code.

Thanks.

(Also thanks for the folks who helped in the old code)

[Tom]

Your description of the problem does not seem to match the issue.

The issue is your pattern for DOB1 is extracting more of the string.

It does not really matter to you date test since you just used the first 10 bytes of the string.  But if you tried to use the macro variable as a date it would not work.

Why aren't you using the solution on the other threads that use the JSON libref engine to parse the JSON string?

[sarahzhou]

Why aren't you using the solution on the other threads that use the JSON libref engine to parse the JSON string?

I tried, 

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

filename json temp;
data json_str;
  file json;
  length string $32767;
  string=symget('input_string');
  put string ;
run;

libname json json fileref=json;

proc print data=json.root (drop=ordinal:) noobs;
run;


data _null_;
  set json_str;
  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',dob_ind);
  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',dob_ind);
run;


%put &=memberid1;
%put &=dob1;
%put &=dob1_ind;
%put &=memberid2;
%put &=dob2;
%put &=dob2_ind;

but this gives dob1_ind and dob2_ind incorrect, 

62         %put &=dob1_ind;
SYMBOLGEN:  Macro variable DOB1_IND resolves to .
DOB1_IND=.
63         %put &=memberid2;
SYMBOLGEN:  Macro variable MEMBERID2 resolves to 'A12345677'
MEMBERID2='A12345677'

I want 

SYMBOLGEN: Macro variable DOB1_IND resolves to 2 if "DOB1": "unknown".

SYMBOLGEN: Macro variable DOB2_IND resolves to 1 if  DOB2": "2022-01-03". Acording to our code.

Thank you

[sarahzhou]

Here is the log,

SYMBOLGEN:  Macro variable MEMBERID1 resolves to '           .'
58         
59         
60         %put &=memberid1;
MEMBERID1='           .'
61         %put &=dob1;
SYMBOLGEN:  Macro variable DOB1 resolves to .
DOB1=.
62         %put &=dob1_ind;
SYMBOLGEN:  Macro variable DOB1_IND resolves to .
DOB1_IND=.
63         %put &=memberid2;
SYMBOLGEN:  Macro variable MEMBERID2 resolves to 'A12345677'
MEMBERID2='A12345677'
3                                                          The SAS System                                09:32 Monday, March 7, 2022

64         %put &=dob2;
SYMBOLGEN:  Macro variable DOB2 resolves to 2022-01-03
DOB2=2022-01-03
65         %put &=dob2_ind;
SYMBOLGEN:  Macro variable DOB2_IND resolves to 1
DOB2_IND=1

[Tom]

Why aren't you using the same data that you printed?

proc print data=json.root (drop=ordinal:) noobs;
run;

[sarahzhou]

The output data is,

How can I use DOB1 and DOB2, and assige them the &DOB1_IND, &DOB2_IND;

they should have assigned like this in our case

SYMBOLGEN:  Macro variable DOB1_IND resolves to 2
DOB1_IND=2

SYMBOLGEN:  Macro variable DOB2_IND resolves to 1
DOB2_IND=1

[sarahzhou]

I've correct the code for the subscripts 1, and 2.

options symbolgen mlogic mlogicnest mprint;
%let input_string =%nrbquote([{"MEMBERID1": "A12345687", "DOB1": "unknown", "MEMBERID2": "A12345677", "DOB2": "2022-01-03"}]);

filename json temp;
data json_str;
  file json;
  length string $32767;
  string=symget('input_string');
  put string ;
run;

libname json json fileref=json;

proc print data=json.root (drop=ordinal:) noobs;
run;


data _null_;
  set json_str;
  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 &=dob1;
%put &=dob1_ind;
%put &=memberid2;
%put &=dob2;
%put &=dob2_ind;

here is the log,

SYMBOLGEN:  Macro variable MEMBERID1 resolves to '           .'
59         
60         
61         %put &=memberid1;
MEMBERID1='           .'
62         %put &=dob1;
SYMBOLGEN:  Macro variable DOB1 resolves to .
DOB1=.
63         %put &=dob1_ind;
SYMBOLGEN:  Macro variable DOB1_IND resolves to 0
DOB1_IND=0
64         %put &=memberid2;
3                                                          The SAS System                                09:32 Monday, March 7, 2022

SYMBOLGEN:  Macro variable MEMBERID2 resolves to '           .'
MEMBERID2='           .'
65         %put &=dob2;
SYMBOLGEN:  Macro variable DOB2 resolves to .
DOB2=.
66         %put &=dob2_ind;
SYMBOLGEN:  Macro variable DOB2_IND resolves to 0
DOB2_IND=0

how can I capture the values for these inputs?

The Macro variable DOB1, Macro variable DOB2, Macro variable DOB1_IND,Macro variable DOB2_IND do not resolve correctly here.