Return a input string as a JSON table

[sarahzhou]

I have a SAS code: 

%let input_string =%nrbquote([{"OrderID": "A0000123", "OrderDate": "4/30/2010"},{"OrderID": "A0000124", "OrderDate": ""}]);

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

data Parse_JSON (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("/(.+?)OrderID(.+?)OrderDate/"); 
		regex02=prxparse("/(.+?)OrderDate(.+?)\}/"); 
	    regex03=prxparse("/(.+?)OrderID(.+?)OrderDate/"); 
		regex04=prxparse("/(.+?)OrderDate(.+?)\}/"); 
	end;

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

    if prxmatch(regex03, json_text) then do;
		OrderID2=strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex03, 2, json_text)),":",""),",",""),'"',""));
    end;
	if prxmatch(regex04, json_text) then do;
		OrderDate2=input(strip(tranwrd(tranwrd(tranwrd(upcase(prxposn(regex04, 2, json_text)),":",""),",",""),'"',"")),YYMMDD10.);
    end;
run;

I want to get the output JSON table as

OrderID1	OrderDate1	OrderID1	OrderDate1
A0000123	4/30/2010	A0000124

However, when I run it, I get

OrderID1	OrderDate1	OrderID1	OrderDate1
A0000123		A0000123

How can I fix my code. Kindly help.

Many many thanks.

[ChrisHemedinger · Accepted solution]

When working with JSON as input, always think of the JSON libname engine first...before resorting to DATA step parsing.

filename j temp;

data _null_;
file j;
put '[{"OrderID": "A0000123", "OrderDate": "4/30/2010"},{"OrderID": "A0000124", "OrderDate": ""}]';
run;

libname data json fileref=j;

data output;
 set data.root;
run;

proc print data=output;
run;

[sarahzhou]

Hi, how do I drop root column?

[ChrisHemedinger]

The ordinal keys are added to help you join multiple tables coming from a single JSON source. 

If it's just the one simple table and you don't need them, drop them like so:

data output;
 set data.root (drop=ordinal:);
run;

[sarahzhou]

Thank you!

[sarahzhou]

Hi Chris,

When I added a macro "input_string" to control the input strings, the below script run with errors

filename j temp;

%let input_string =%nrbquote({"OrderID1": "A0000123", "OrderDate1": "4/30/2010","OrderID2": "A0000124", "OrderDate2": ""});

data _null_;
file j;

put &input_string;

run;

libname data json fileref=j;

data output;
 set data.root (drop=ordinal:);
run;

proc print data=output;
run;

I read the document https://blogs.sas.com/content/sasdummy/2018/07/05/if-then-else-sas-programs/

It shoud give me the expected output.

Can you advise?

How can I modify my code.

Thank you!

[ChrisHemedinger]

Instead of using NRBQUOTE (which is used for masking values but doesn't produce a quoted string literal), simply use single quotes in the macro variable assignment.

filename j temp;

%let input_string ='{"OrderID1": "A0000123", "OrderDate1": "4/30/2010","OrderID2": "A0000124", "OrderDate2": ""}';

data _null_;
file j;

put &input_string;

run;

libname data json fileref=j;

data output;
 set data.root (drop=ordinal:);
run;

proc print data=output;
run;

[sarahzhou]

oh, I see now. Thanks!

[sarahzhou]

Hi,

The  input string does not controlled by my end , I have to follow the formate : %let input_string =%nrbquote([{"MEMBERID1": "A12345690", "DOB1": "", "MEMBERID2": "A12345677", "DOB2": "2022-Mar-03"}]);

then I tried to remove the square breaket by %sysfunc(compress(&input_string., "[]")) and it gives me error.

The updated script as below:

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

%let input_string_nb =%sysfunc(compress(&input_string., "[]"));
%put &input_string_nb.;
filename j temp;
data _null_;
file j;
put &input_string_nb.;
run;
libname data json fileref=j;

data output;
 set data.root (drop=ordinal:);
run;

proc print data=output noobs;
run;

How do I fix this?

[Tom]

Why did you ask COMPRES() to remove the double quote character in addition to the open and close square bracket characters?  Take the two double quote characters out of the list of characters you want COMPRESS() to remove.

%let input_string_nb =%sysfunc(compress(&input_string.,[]));

You used the macro processor to generate an invalid PUT statement.  This is not valid SAS code.

put {"MEMBERID1": "A12345690", "DOB1": "", "MEMBERID2": "A12345677", "DOB2": "2022-Mar-03"};

If you are going to use the macro processor to generate SAS code you need to have it generate valid SAS code.

You will find it a lot easier to use normal SAS code to manipulate strings.  Plus in they case there is no need to remove the [ ] characters. In other cases removing them will destroy the JSON text.

%let input_string=
[{"MEMBERID1": "A12345690", "DOB1": "", "MEMBERID2": "A12345677", "DOB2": "2022-Mar-03"}]
;

filename json temp;
data _null_;
  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;

Results:

MEMBERID1    DOB1    MEMBERID2       DOB2

A12345690            A12345677    2022-Mar-03

[sarahzhou]

Hi, 

How can I call the MEMBERID1, DOB1,MEMBERID2, DOB2 for later use, I need to call DOB 1 and DOB 2 to do some logic comparation.

Thanks

[Tom]

---

@sarahzhou wrote:

Hi, 

How can I call the MEMBERID1, DOB1,MEMBERID2, DOB2 for later use, I need to call DOB 1 and DOB 2 to do some logic comparation.

Thanks

---

I really have no idea what you are asking about.

 Now that you have data in a dataset just use normal SAS code to use it.  The same way you would use the values of NAME or AGE from SASHELP.CLASS. 

[sarahzhou]

Thank you.

[Tom]

With simple regular strings like that I find that SCAN() is easier to use.

data test;
  json='[{"OrderID": "A0000123", "OrderDate": "4/30/2010"},{"OrderID": "A0000124", "OrderDate": ""}]';
  dlm='[]{}: ,';
  do index=1 to countw(json,dlm,'q');
    name=dequote(scan(json,index,dlm,'q'));
    index+1;
    value=dequote(scan(json,index,dlm,'q'));
    column+(name='OrderID');
    output;
  end;
  drop dlm index;
run;

proc print;
 by json ;
run;

Result

json=[{"OrderID": "A0000123", "OrderDate": "4/30/2010"},{"OrderID": "A0000124", "OrderDate": ""}]

Obs      name         value      column

 1     OrderID      A0000123        1
 2     OrderDate    4/30/2010       1
 3     OrderID      A0000124        2
 4     OrderDate                    2