SAS DI 4.9 JSON Column Parse Data Step

[vfarmak]

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

[ChrisNZ]

There are 2 steps here: 

1. Define the variable names

2. Populate the table with data

Are you after both?

[vfarmak]

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?

[ChrisNZ · Accepted solution]

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

[vfarmak]

Thank you very much! It worked like a charm. This json field is always generated like this so the logic fits the purpose!

[ChrisNZ]

You might want to alter the last scan and add option 'm', in case NOTES is not empty.

[vfarmak]

Thanks for the tip @ChrisNZ

[LinusH]

I guess this resided in separate Json file, and I think you could take a look at the Json Libname engine.

[vfarmak]

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

[LinusH]

https://blogs.sas.com/content/sasdummy/2016/12/02/json-libname-engine-sas/

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsglobal&docsetTarge...