topic Re: Need help to loop, read, write dataset after JSON file is SAS in SAS Programming

Need help to loop, read, write dataset after JSON file is SAS

niejung — Tue, 14 Jul 2020 20:19:04 GMT

I am a beginner for this and working on old SAS version and I am not familiar DS2 ( although I saw many great posts and I just not able to get it to work the way I want) I am using INFILE and able to follow some instructions written by others and load the data into SAS. I am honestly looking at the many posts and confused.

filename rept "out.json";

data x;
infile rept lrecl=320000 dsd dlm='{}[],';
input x : $400. @@;
if not missing(x);
run;
data want;
set x;
name=dequote(strip(scan(x,1,':','mq')));
value=dequote(strip(scan(x,-1,':','mq')));

run;
proc print data=want;
run;

"MetaData" :	MetaData
"ResponseTime" : "2020-07-08 15:21:42"	ResponseTime	7/8/2020 15:21
"StatusType" : "COMPLETE"	StatusType	COMPLETE
"PriceAsOfDateTime" : "2020-05-29 19:00:00"	PriceAsOfDateTime	5/29/2020 19:00
"ProductResults" :	ProductResults
"Product" : "ABC EDF XXX 3/1 2/2/5"	Product	ABC EDF XXX 3/1 2/2/5
"Results" :	Results
"Price" : 1.00921875	Results	1.00921875
"Coupon" : 0.01	Coupon	0.01
"Price" : 1.01171875	Price	1.01171875
"Coupon" : 0.01125	Coupon	0.01125
"Price" : 1.01421875	Price	1.01421875
"Coupon" : 0.0125	Coupon	0.0125
"Product" : "ABC EDF XXX 3/1 10/5/5"	Product	ABC EDF XXX 3/1 10/5/5
"Results" :	Results
"Price" : 1.00921875	Results	1.04185547
"Coupon" : 0.01	Coupon	0.0275
"Price" : 1.01171875	Price	1.04436524
"Coupon" : 0.01125	Coupon	0.02875
"Price" : 1.01421875	Price	1.046875
"Coupon" : 0.0125	Coupon	0.03

Output I want. How can I get to that?

PriceAsOfDateTime	Product	Results	Coupon
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.00921875	0.01
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.01171875	0.01125
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.01421875	0.0125
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.04185547	0.0275
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.04436524	0.02875
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.046875	0.03

Re: Need help to loop, read, write dataset after JSON file is SAS

smantha — Tue, 14 Jul 2020 20:30:17 GMT

filename rept "out.json";
/*if fields are text */
data x;
Length name $32. Value $200;
infile rept lrecl=320000 dsd dlm='{}[],';
input x : $400. @@;
if not missing(x);
name=dequote(strip(scan(x,1,':','mq')));
value=dequote(strip(scan(x,-1,':','mq')));
run;
proc transpose data = x out=want;
run;

Did you try libname json https://documentation.sas.com/?docsetId=lestmtsglobal&docsetTarget=n1jfdetszx99ban1rl4zll6tej7j.htm&docsetVersion=9.4&locale=en

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Tue, 14 Jul 2020 21:20:52 GMT

libname json is not available for me to use. 😭

Re: Need help to loop, read, write dataset after JSON file is SAS

Reeza — Tue, 14 Jul 2020 21:30:10 GMT

OP says they were on an older version.

OP can you specify what version exactly? The following code will share your version.

If you're on a slightly later version there are DS2 options but I admit that I always found the data step simpler.

proc product_status;run;

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Tue, 14 Jul 2020 21:55:49 GMT

Will this help?

For Base SAS Software ...
Custom version information: 9.4_M3
Image version information: 9.04.01M3P060315
For SAS/STAT ...
Custom version information: 14.1
For SAS/GRAPH ...
Custom version information: 9.4_M3
For SAS/ETS ...
Custom version information: 14.1
For SAS/FSP ...
Custom version information: 9.4_M3
For SAS/OR ...
Custom version information: 14.1

Re: Need help to loop, read, write dataset after JSON file is SAS

Reeza — Tue, 14 Jul 2020 22:11:01 GMT

Yeah, 9.4M4 has the JSON support, one version up. It's been around since 2016 so your org is definitely due for an upgrade.

I'll see if I can find some time later tonight to test this, but with RETAIN and some coding you can get what you need fairly easily. Could you attach an exact example of the JSON response? If it's via a public API the link is fine too.

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Tue, 14 Jul 2020 22:23:46 GMT

Attached.
My thought was to split into 2 datasets. One is for status (top 6 rows) and details on rest of them.

then try to use SQL builder to merge to table together. So far I am not moving anywhere. 😫

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Wed, 15 Jul 2020 01:53:01 GMT

This is what I did so far. I did try to use DS2.

/* Put some JSON in a macro variable for use in testing */

%let json= '{
"PricingRequestMetaData": {
"CorrelationIdentifier": "949cf51d-3689-4d51-966f-8a193fd1487c",
"ResponseTime": "2020-07-14 18:17:53",
"PricingStatusType": "COMPLETE"
},
"PricingResponsePayload": {
"PriceAsOfDateTime": "2020-05-29 19:00:00",
"BenchmarkSecurityProductResults": [
{
"BenchmarkSecurityProduct": "FHLMC ARM CMS 3/1 2/2/5",
"CouponResults": [
{
"ArmPrice": 1.00921875,
"SecurityCoupon": 0.01
},
{
"ArmPrice": 1.01171875,
"SecurityCoupon": 0.01125
},
{
"ArmPrice": 1.01421875,
"SecurityCoupon": 0.0125
},
{
"ArmPrice": 1.01671875,
"SecurityCoupon": 0.01375
}
]
},
{
"BenchmarkSecurityProduct": "FHLMC ARM CMS 5/1 2/2/5",
"CouponResults": [
{
"ArmPrice": 1.00980469,
"SecurityCoupon": 0.01
},
{
"ArmPrice": 1.01375977,
"SecurityCoupon": 0.01125
},
{
"ArmPrice": 1.01771485,
"SecurityCoupon": 0.0125
}
]
}
]
}
}';

proc ds2;
data tokens/overwrite=yes;
dcl package json j();
dcl nchar(1024) token;
dcl int tokenType;
dcl char(1) string number bool_true bool_false delimiter;
method init();
dcl varchar(1000000) json;
dcl nchar(1024) thisVar;
dcl int rc parseFlags;
/* JSON for prototyping */
json=%superq(json);
rc = j.createParser();
if (rc) then do;
put 'Error' rc= ': Could not create JSON parser.';
stop;
end;
rc = j.setParserInput(json);
if (rc) then do;
put 'Error' rc= ': setParserInput failed.';
stop;
end;

/* Use the parser to parse the JSON */
/* RC of 0 means all went well. Otherwise, an error occurred */
do until (rc ne 0);
j.getNextToken( rc, token, tokenType, parseFlags );
string=if j.isstring(tokenType) then 'Y' else 'N';
number=if j.isnumeric(tokenType) then 'Y' else 'N';
bool_true=if j.isbooleantrue(tokenType) then 'Y' else 'N';
bool_false=if j.isbooleanfalse(tokenType) then 'Y' else 'N';
delimiter=if tokenType in (16,32,64,128) then 'Y' else 'N';

output;
end;
end;
enddata;
run;
quit;

%_eg_conditional_dropds(WORK.QUERY_FOR_FINAL);

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FINAL AS
SELECT t1.token,
t1.tokenType,
t1.string,
t1.number,
t1.bool_true,
t1.bool_false,
t1.delimiter
FROM WORK.FINAL t1
WHERE t1.tokenType > 128;
QUIT;

proc print data=WORK.QUERY_FOR_FINAL;
run;

How can I create data format like below:

PriceAsOfDateTime	Product	Results	Coupon
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.00921875	0.01
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.01171875	0.01125
5/29/2020 19:00	ABC EDF XXX 3/1 2/2/5	1.01421875	0.0125
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.04185547	0.0275
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.04436524	0.02875
5/29/2020 19:00	ABC EDF XXX 3/1 10/5/5	1.046875	0.03

Re: Need help to loop, read, write dataset after JSON file is SAS

Reeza — Thu, 16 Jul 2020 17:45:01 GMT

data process_request;

	infile '/home/fkhurshed/sampledata.txt';
	input;
	str=_infile_;
	
	length CorrelationIdentifier $50.;
	retain CorrelationIdentifier ResponseTime PricingStatusType PriceAsofDateTime 
		BenchmarkSecurityProduct ArmPrice;

	if index(str, "CorrelationIdentifier")>0 then
		CorrelationIdentifier=scan(str, 3, ':"');
	else if index(str, "ResponseTime")>0 then
		ResponseTime=input(scan(str, 3, '"'), anydtdtm.);
	else if index(str, "PricingStatusType")>0 then
		PricingStatusType=scan(str, 3, '"');
	else if index(str, "PriceAsOfDateTime")>0 then
		PriceAsOfDateTime=input(scan(str, 3, '"'), anydtdtm.);
	else if index(str, "BenchmarkSecurityProduct")>0 then
		BenchmarkSecurityProduct=scan(str, 3, '"');
	else if index(str, "ArmPrice")>0 then
		ArmPrice=input(scan(str, 2, ':,'), best12.);
	else if index(str, "SecurityCoupon")>0 then
		do;
			SecurityCoupon=input(scan(str, 2, ':,'), best12.);
			output;
			ArmPrice=.;
		end;
	format responseTime PriceAsOfDateTime datetime20. ArmPrice 12.8 SecurityCoupon 
		12.5;
		
	drop str;
run;

This processes your demo output. As long as it has the same format and only one BenchmarkSecurityProduct per response you're good. If you need to consider multiple the data step would need to be modified but it's doable.

@niejung wrote:

This is what I did so far. I did try to use DS2.

/* Put some JSON in a macro variable for use in testing */

%let json= '{
"PricingRequestMetaData": {
"CorrelationIdentifier": "949cf51d-3689-4d51-966f-8a193fd1487c",
"ResponseTime": "2020-07-14 18:17:53",
"PricingStatusType": "COMPLETE"
},
"PricingResponsePayload": {
"PriceAsOfDateTime": "2020-05-29 19:00:00",
"BenchmarkSecurityProductResults": [
{
"BenchmarkSecurityProduct": "FHLMC ARM CMS 3/1 2/2/5",
"CouponResults": [
{
"ArmPrice": 1.00921875,
"SecurityCoupon": 0.01
},
{
"ArmPrice": 1.01171875,
"SecurityCoupon": 0.01125
},
{
"ArmPrice": 1.01421875,
"SecurityCoupon": 0.0125
},
{
"ArmPrice": 1.01671875,
"SecurityCoupon": 0.01375
}
]
},
{
"BenchmarkSecurityProduct": "FHLMC ARM CMS 5/1 2/2/5",
"CouponResults": [
{
"ArmPrice": 1.00980469,
"SecurityCoupon": 0.01
},
{
"ArmPrice": 1.01375977,
"SecurityCoupon": 0.01125
},
{
"ArmPrice": 1.01771485,
"SecurityCoupon": 0.0125
}
]
}
]
}
}';

proc ds2;
data tokens/overwrite=yes;
dcl package json j();
dcl nchar(1024) token;
dcl int tokenType;
dcl char(1) string number bool_true bool_false delimiter;
method init();
dcl varchar(1000000) json;
dcl nchar(1024) thisVar;
dcl int rc parseFlags;
/* JSON for prototyping */
json=%superq(json);
rc = j.createParser();
if (rc) then do;
put 'Error' rc= ': Could not create JSON parser.';
stop;
end;
rc = j.setParserInput(json);
if (rc) then do;
put 'Error' rc= ': setParserInput failed.';
stop;
end;

/* Use the parser to parse the JSON */
/* RC of 0 means all went well. Otherwise, an error occurred */
do until (rc ne 0);
j.getNextToken( rc, token, tokenType, parseFlags );
string=if j.isstring(tokenType) then 'Y' else 'N';
number=if j.isnumeric(tokenType) then 'Y' else 'N';
bool_true=if j.isbooleantrue(tokenType) then 'Y' else 'N';
bool_false=if j.isbooleanfalse(tokenType) then 'Y' else 'N';
delimiter=if tokenType in (16,32,64,128) then 'Y' else 'N';

output;
end;
end;
enddata;
run;
quit;

%_eg_conditional_dropds(WORK.QUERY_FOR_FINAL);

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FINAL AS
SELECT t1.token,
t1.tokenType,
t1.string,
t1.number,
t1.bool_true,
t1.bool_false,
t1.delimiter
FROM WORK.FINAL t1
WHERE t1.tokenType > 128;
QUIT;

proc print data=WORK.QUERY_FOR_FINAL;
run;

How can I create data format like below:

PriceAsOfDateTime Product Results Coupon

5/29/2020 19:00 ABC EDF XXX 3/1 2/2/5 1.00921875 0.01

5/29/2020 19:00 ABC EDF XXX 3/1 2/2/5 1.01171875 0.01125

5/29/2020 19:00 ABC EDF XXX 3/1 2/2/5 1.01421875 0.0125

5/29/2020 19:00 ABC EDF XXX 3/1 10/5/5 1.04185547 0.0275

5/29/2020 19:00 ABC EDF XXX 3/1 10/5/5 1.04436524 0.02875

5/29/2020 19:00 ABC EDF XXX 3/1 10/5/5 1.046875 0.03

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Thu, 16 Jul 2020 22:30:14 GMT

This is great!! I was struggle how I can get file into string. Let me play around a little. 😘

	infile '/home/fkhurshed/sampledata.txt';
	input;
	str=_infile_;

Re: Need help to loop, read, write dataset after JSON file is SAS

Reeza — Fri, 17 Jul 2020 15:27:41 GMT

Is the JSON from an API response?

Re: Need help to loop, read, write dataset after JSON file is SAS

niejung — Sat, 18 Jul 2020 19:59:42 GMT

Yes. I saved the response output to a file.
I was able to fixed the parsing for product information. I am REALLY appreciated your help. I learned so much for this and this is first time doing SAS development.

For the product, I changed to
Substr(str, index(str,’: ‘), lengthn(str)-38);