Greetings everyone, I am facing the following issue: I have loaded a dataset in SASDI that some columns contain json arrays. The problem with json arrays is that do not have always the same length. At first I used the technique provided by the below thread https://communities.sas.com/t5/SAS-Programming/SAS-DI-4-9-JSON-Column-Parse-Data-Step/m-p/684905#M207646 by ChrisZ and it works fine with one size arrays. When it comes to arrays that have size greater than one, the code reads only the first line. The expected outcome is to generate in the below example lines that are associated with the number of the array elements. id content 000001 [{"item":"value1"}, {"item":"value2"},{"item":"value3"}] to id value 000001 value1 000001 value2 000001 value3 I searched for the following: SCANOVER function: this comes in handy but I think that it can be used only in the infile statement I tried the PROC DS2 with no luck: I was able to execute the code but when the parser is initialized; it does not see any of the tokens provided in the json column JSON lib does not supported by the version of our SAS system Below you will find the attempt with the PROC DS2 & the sas version Contents of HAVE Dataset _id contactProgress 5fa1b999c2dc2170299dc0b4 [{"date": { "$date": "2020-11-04T15:20:00.000Z" }, "details": {"code": "040", "description": "Follow up"}, "feedback": {"code": "020", "description": "No answer"}, "notes": "this is the content of the text provided, "type": "INPROGRESS"}, {"date": { "$date": "2020-11-04T18:00:00.000Z" }, "details": {"code": "040", "description": "Follow up"}, "feedback": {"code": "010", "description": "Communication"}, "notes": "This is the new note test", "type": "INPROGRESS"}, {"date": { "$date": "2020-11-05T16:30:00.000Z" }, "details": {"code": "040", "description": "Follow up"}, "feedback": {"code": "010", "description": "Communication"}, "notes": "The test quotation has been sent to the client", "type": "INPROGRESS"}] DATA HAVE;
SET WORK.QUERY_FOR_WRK_LEADS_KPIS;
RUN;
PROC DS2;
DATA WANT (OVERWRITE=YES);
DCL PACKAGE JSON J();
DCL INTEGER TOKENTYPE PARSEFLAGS;
DCL NVARCHAR(128) TOKEN;
DCL INTEGER RC;
DCL CHAR(2000) CHARACTER SET UTF8 CONTACT_PROGRESS;
METHOD INIT();
RC = J.CREATEPARSER( CONTACT_PROGRESS, 32767 );
DO WHILE( RC = 0 );
J.GETNEXTTOKEN( RC, TOKEN, TOKENTYPE, PARSEFLAGS );
IF (TOKEN = '') THEN do;
%put 'Empty Token Found';
end;
END;
END;
METHOD RUN();
SET WORK.HAVE;
END;
method term();
rc = j.destroyParser();
end;
ENDDATA;
RUN; Thank you in advance for your time.
... View more
