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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.