topic Re: SAS DI 4.9 JSON Column Parse Data Step in SAS Programming

SAS DI 4.9 JSON Column Parse Data Step

vfarmak — Thu, 17 Sep 2020 22:32:48 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

ChrisNZ — Fri, 18 Sep 2020 05:02:39 GMT

There are 2 steps here:

1. Define the variable names

2. Populate the table with data

Are you after both?

Re: SAS DI 4.9 JSON Column Parse Data Step

vfarmak — Fri, 18 Sep 2020 05:05:52 GMT

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?

Re: SAS DI 4.9 JSON Column Parse Data Step

ChrisNZ — Fri, 18 Sep 2020 07:23:57 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

LinusH — Fri, 18 Sep 2020 07:25:08 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

vfarmak — Fri, 18 Sep 2020 07:31:56 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

vfarmak — Fri, 18 Sep 2020 07:33:30 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

ChrisNZ — Fri, 18 Sep 2020 07:49:51 GMT

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

Re: SAS DI 4.9 JSON Column Parse Data Step

vfarmak — Fri, 18 Sep 2020 07:51:52 GMT

Thanks for the tip @ChrisNZ

Re: SAS DI 4.9 JSON Column Parse Data Step

LinusH — Fri, 18 Sep 2020 09:40:39 GMT

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&docsetTarget=n1jfdetszx99ban1rl4zll6tej7j.htm&locale=en