topic Re: Return a input string as a JSON table in SAS Programming

Return a input string as a JSON table

sarahzhou — Thu, 06 Jan 2022 13:34:54 GMT

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.

Re: Return a input string as a JSON table

ChrisHemedinger — Thu, 06 Jan 2022 14:07:42 GMT

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;

Re: Return a input string as a JSON table

Tom — Thu, 06 Jan 2022 14:09:32 GMT

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

Re: Return a input string as a JSON table

sarahzhou — Thu, 06 Jan 2022 15:22:16 GMT

Hi, how do I drop root column?

Re: Return a input string as a JSON table

ChrisHemedinger — Thu, 06 Jan 2022 15:30:40 GMT

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;

Re: Return a input string as a JSON table

sarahzhou — Thu, 06 Jan 2022 16:07:24 GMT

Thank you!

Re: Return a input string as a JSON table

sarahzhou — Fri, 07 Jan 2022 05:32:25 GMT

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!

Re: Return a input string as a JSON table

ChrisHemedinger — Fri, 07 Jan 2022 12:22:27 GMT

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;

Re: Return a input string as a JSON table

sarahzhou — Fri, 07 Jan 2022 13:37:43 GMT

oh, I see now. Thanks!

Re: Return a input string as a JSON table

sarahzhou — Fri, 04 Mar 2022 17:17:19 GMT

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?

Re: Return a input string as a JSON table

Tom — Fri, 04 Mar 2022 20:51:54 GMT

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

Re: Return a input string as a JSON table

sarahzhou — Mon, 07 Mar 2022 04:02:56 GMT

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

Re: Return a input string as a JSON table

Tom — Mon, 07 Mar 2022 05:56:34 GMT

@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.

Re: Return a input string as a JSON table

sarahzhou — Mon, 07 Mar 2022 06:24:32 GMT

Thank you.