Greetings Everyone!
I am currently using SAS DI 4.9 and I have a SAS Dataset with a column that stores json data.
To give you an example, a sample value for the column is the following:
'[{"date": { "$date": "2020-09-15T11:00:00.000Z" }, "details": {"code": "050", "album": "IV"}, "feedback": {"code": "020", "band": "Led Zepellin"}, "notes": "", "type": "SUCCESSFUL"}]';
I want to extract the data and place it in a table. I know that this can be done with user written component.
Which are the commands in the data step in order to extract the data in a tabular format?
The format that i want to be displayed is the following.
Data | Details.Code | Details.Album | Feedback.Code | Feedback.Band | Notes | Type |
2020-09-15T11:00:00.000Z | 50 | IV | 20 | Led Zepellin | SUCCESSFUL |
Thank you in advance,
Vasilios
A very static logic:
data HAVE;
STR='[{"date": { "$date": "2020-09-15T11:00:00.000Z" }, "details": {"code": "050", "album": "IV"}, "feedback": {"code": "020", "band": "Led Zepellin"}, "notes": "", "type": "SUCCESSFUL"}]';
DATE =scan(STR, 6,'"');
DETAILS_CODE =scan(STR,12,'"');
DETAILS_ALBUM=scan(STR,16,'"');
FEEDBACK_CODE=scan(STR,22,'"');
FEEDBACK_BAND=scan(STR,26,'"');
NOTES =scan(STR,30,'"','m');
TYPE =scan(STR,33,'"');
run;
DATE | DETAILS_CODE | DETAILS_ALBUM | FEEDBACK_CODE | FEEDBACK_BAND | NOTES | TYPE |
---|---|---|---|---|---|---|
2020-09-15T11:00:00.000Z | 050 | IV | 020 | Led Zepellin | SUCCESSFUL |
There are 2 steps here:
1. Define the variable names
2. Populate the table with data
Are you after both?
Yes.
The resulted dataset should be like the table mentioned above.
The first step is ok.
How can I populate the table with data via datastep?
A very static logic:
data HAVE;
STR='[{"date": { "$date": "2020-09-15T11:00:00.000Z" }, "details": {"code": "050", "album": "IV"}, "feedback": {"code": "020", "band": "Led Zepellin"}, "notes": "", "type": "SUCCESSFUL"}]';
DATE =scan(STR, 6,'"');
DETAILS_CODE =scan(STR,12,'"');
DETAILS_ALBUM=scan(STR,16,'"');
FEEDBACK_CODE=scan(STR,22,'"');
FEEDBACK_BAND=scan(STR,26,'"');
NOTES =scan(STR,30,'"','m');
TYPE =scan(STR,33,'"');
run;
DATE | DETAILS_CODE | DETAILS_ALBUM | FEEDBACK_CODE | FEEDBACK_BAND | NOTES | TYPE |
---|---|---|---|---|---|---|
2020-09-15T11:00:00.000Z | 050 | IV | 020 | Led Zepellin | SUCCESSFUL |
You might want to alter the last scan and add option 'm', in case NOTES is not empty.
Thanks for the tip @ChrisNZ
I guess this resided in separate Json file, and I think you could take a look at the Json Libname engine.
@LinusH I performed a search but most of the examples were focusing on how to extract sas fields to json format.
Do you have any example for parsing in mind?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.