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 |
Click image to register for webinar
