BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vfarmak
Quartz | Level 8

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.

DataDetails.CodeDetails.AlbumFeedback.CodeFeedback.BandNotesType
2020-09-15T11:00:00.000Z50IV20Led Zepellin SUCCESSFUL

 

Thank you in advance,

Vasilios

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

 

View solution in original post

9 REPLIES 9
ChrisNZ
Tourmaline | Level 20

There are 2 steps here: 

1. Define the variable names

2. Populate the table with data

Are you after both?

 

vfarmak
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8
Thank you very much! It worked like a charm. This json field is always generated like this so the logic fits the purpose!
ChrisNZ
Tourmaline | Level 20

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

vfarmak
Quartz | Level 8

Thanks for the tip @ChrisNZ

LinusH
Tourmaline | Level 20

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

Data never sleeps
vfarmak
Quartz | Level 8

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2392 views
  • 2 likes
  • 3 in conversation