topic Re: Convert JSON file into SAS readable dataset in SAS Programming

Convert JSON file into SAS readable dataset

yichentian226 — Wed, 06 Oct 2021 21:38:07 GMT

Hi everyone,

I have the JSON file and I need to convert this JSON file, that is in text format, into a SAS dataset. Attached is the example JSON text file. Any help or suggestion is appreciated.

Schema in the table:

_id

barcode

brandCode

category

categoryCode

cpg

topBrand

name

Sample:

{"_id":{"$oid":"601ac115be37ce2ead437551"},"barcode":"511111019862","category":"Baking","categoryCode":"BAKING","cpg":{"$id":{"$oid":"601ac114be37ce2ead437550"},"$ref":"Cogs"},"name":"test brand @1612366101024","topBrand":false}
{"_id:{"$oid":"601c5460be37ce2ead43755f"},"barcode":"511111519928","brandCode":"STARBUCKS","category":"Beverages","categoryCode":"BEVERAGES","cpg":{"$id":{"$oid":"5332f5fbe4b03c9a25efd0ba"},"$ref":"Cogs"},"name":"Starbucks","topBrand":false}

Re: Convert JSON file into SAS readable dataset

Reeza — Wed, 06 Oct 2021 21:44:22 GMT

Have you tried saving the file to a text file/JSON file and then using libname JSON?
https://blogs.sas.com/content/sasdummy/2016/12/02/json-libname-engine-sas/

Re: Convert JSON file into SAS readable dataset

yichentian226 — Wed, 06 Oct 2021 22:12:21 GMT

Thanks for the reply. I read that article earlier and got error while trying to run it myself. I guess I wasn't understanding the "proc http
url= "&fullurl.", in this case will I need to save the file in a web path? My file is saved as txt on the desktop.