Accepted Solutions
@Tal,
The below code will parse the JSON that you included in your original post. This code has the advantage that one does not need to know the variable names beforehand. This is somewhat unsophisticated code in that I'm not checking for embedded commas in either the variable names or the values. If there are embedded commas, this code would have to be modified.
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Txt_Wrk1 $1026;
LENGTH Var $32;
LENGTH Value $1026;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Txt_Wrk1 = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
Var = COMPRESS(SUBSTR(_Txt_Wrk1, 1, (INDEXC(_Txt_Wrk1, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Txt_Wrk1, (INDEXC(_Txt_Wrk1, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
OUTPUT;
END;
RUN;The above code yields the following results:
Jim
Well, I was thinking about this just a bit more. If one is content with collecting a series of variable names and their values by parsing the JSON data, then my previous reply is sufficient.
However, if one were to desire to have the variable names become separate variable names in a SAS data set, that's a bit more involved. There are no doubt other ways to do this, but since I rather like macros, I coded the following:
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
**------------------------------------------------------------------------------**;
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Text_Work $1026;
LENGTH _Var_Name $32;
LENGTH Value $1026;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
_Iteration = 0;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Iteration + 1;
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Text_Work = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
_Var_Name = COMPRESS(SUBSTR(_Text_Work, 1, (INDEXC(_Text_Work, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Text_Work, (INDEXC(_Text_Work, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
IF _N_ = 1 THEN
CALL SYMPUTX(CATS('Var_Name', PUT(_Iteration, F3.)), _Var_Name);
OUTPUT;
END;
IF _N_ = 1 THEN
CALL SYMPUTX('Var_Count', PUT(_Iteration, F3.));
RUN;
**------------------------------------------------------------------------------**;
%MACRO List_Vars(Comma=NO);
%IF %QUPCASE(&Comma) = YES %THEN
%DO;
%LET Comma = ,;
%END;
%ELSE
%DO;
%LET Comma = ;
%END;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
%IF &i = &Var_Count %THEN
%LET Comma = ;
&&Var_Name&i &Comma
%END;
%MEND List_Vars;
**------------------------------------------------------------------------------**;
%MACRO Equate_Vars;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
IF _Iteration = &i THEN
&&Var_Name&i = Value;
%END;
%MEND Equate_Vars;
**------------------------------------------------------------------------------**;
DATA WORK.Final_Data;
DROP Value;
DROP _:;
RETAIN _Iteration;
RETAIN %List_Vars;
SET WORK.PARSED_DATA;
_Iteration + 1;
IF _Iteration > &Var_Count THEN
_Iteration = 1;
%Equate_Vars;
IF _Iteration = &Var_Count THEN
DO;
OUTPUT;
CALL MISSING(%List_Vars(Comma=YES));
END;
RUN;
**------------------------------------------------------------------------------**;
The result of which is a SAS data set with column names parsed out from the JSON data:
This of course assumes that every iteration of the JSON data has the same set of variables. If all the variable names are not the same, then I think the JSON files would have to be parsed into separate SAS data sets which doesn't sound very practical.
Jim
@Tal,
It was an interesting exercise, so I went ahead and wrote code that can create SAS variables based on JSON formatted data even if not all of the same variables are in each instance of JSON formatted data. If you ever need it, here it is. 🙂
As test data, I added a few more rows. Some of the additional rows have columns that previous rows do not. The added columns are:
- new
- vip
- special
- promo
Here are the results. Note that for rows in which the JSON data did not contain a value for a given column that the value of that column is "missing".
Here's the code. I hope it comes in handy some time:
%Time_Stamp(START);
**------------------------------------------------------------------------------**;
OPTIONS VALIDVARNAME = V7;
%LET Cmnt = ;
%LET Width = 75;
**------------------------------------------------------------------------------**;
FILENAME In_Data 'C:\Users\jbarbou3\Documents\SAS\Pgm\Training\JSON_Parse\JSON_Data.txt';
**------------------------------------------------------------------------------**;
DATA WORK.Parsed_Data;
DROP _:;
LENGTH _JSON_Data $32767;
LENGTH _Segment $1026;
LENGTH _Var_Name $32;
LENGTH Row_Nbr 4;
LENGTH Var_Nbr 4;
LENGTH Value $1026;
RETAIN _Max_Nbr 0;
IF _N_ = 1 THEN
DO;
CALL MISSING(Var_Nbr, _Var_Name);
DECLARE HASH Vars(ORDERED: 'A');
Vars.DEFINEKEY('_Var_Name');
Vars.DEFINEDATA('Var_Nbr');
Vars.DEFINEDONE();
END;
IF _End_of_Data THEN
DO;
CALL SYMPUTX('Var_Count', PUT(_Max_Nbr, F3.), 'G');
END;
INFILE In_Data LENGTH = _JSON_Length
RECFM = V
END = _End_of_Data
;
INPUT @1 _JSON_Data $VARYING32767. _JSON_Length;
** The first character is a {, so start in position 2. **;
_Position = 2;
SUBSTR(_JSON_Data, _JSON_Length, 1) = ',';
DO WHILE (_Position < _JSON_Length);
_Segment_Length = INDEXC(SUBSTR(_JSON_Data, _Position), ',');
_Segment = SUBSTR(_JSON_Data, _Position, (_Segment_Length - 1));
_Var_Name = COMPRESS(SUBSTR(_Segment, 1, (INDEXC(_Segment, ':') - 1)), '"');
Value = COMPRESS(SUBSTR(_Segment, (INDEXC(_Segment, ':') + 1)), '"');
_Position = _Position + _Segment_Length;
_RC = Vars.FIND();
IF _RC = 0 THEN
DO;
CALL SYMPUTX(CATS('Var_Name', PUT(Var_Nbr, F3.)), _Var_Name, 'G');
IF Var_Nbr > _Max_Nbr THEN
_Max_Nbr = Var_Nbr;
END;
ELSE
DO;
_Max_Nbr + 1;
CALL SYMPUTX(CATS('Var_Name', PUT(_Max_Nbr, F3.)), _Var_Name, 'G');
Var_Nbr = _Max_Nbr;
_RC = Vars.ADD();
IF _RC > 0 THEN
DO;
PUTLOG "&Err2 ";
PUTLOG "&Err2 %Format_Dashes(&Width)";
PUTLOG "&Err1 | Unable to add variable to Hash table. " _RC=;
PUTLOG "&Err2 %Format_Dashes(&Width)";
END;
END;
Row_Nbr = _N_;
OUTPUT;
END;
RUN;
**------------------------------------------------------------------------------**;
&Cmnt.PROC PRINT DATA=WORK.PARSED_DATA;
&Cmnt.RUN;
**------------------------------------------------------------------------------**;
%MACRO List_Vars(Comma=NO);
%IF %QUPCASE(&Comma) = YES %THEN
%DO;
%LET Comma = ,;
%END;
%ELSE
%DO;
%LET Comma = ;
%END;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
%IF &i = &Var_Count %THEN
%LET Comma = ;
&&Var_Name&i &Comma
%END;
%MEND List_Vars;
&Cmnt%PUT &Nte1 &=Var_Count %List_Vars(Comma=YES);
**------------------------------------------------------------------------------**;
%MACRO Equate_Vars;
%LOCAL i;
%DO i = 1 %TO &Var_Count;
IF Var_Nbr = &i THEN
&&Var_Name&i = Value;
%END;
%MEND Equate_Vars;
&Cmnt%PUT &Nte1 %QUOTE(%Equate_Vars);
**------------------------------------------------------------------------------**;
DATA WORK.Final_Data;
DROP Row_Nbr;
DROP Var_Nbr;
DROP Value;
DROP _:;
RETAIN %List_Vars;
SET WORK.PARSED_DATA;
BY Row_Nbr;
%Equate_Vars;
IF LAST.Row_Nbr THEN
DO;
OUTPUT;
CALL MISSING(%List_Vars(Comma=YES));
END;
RUN;
**------------------------------------------------------------------------------**;
&Cmnt.PROC PRINT DATA=WORK.FINAL_DATA;
&Cmnt.RUN;
**------------------------------------------------------------------------------**;
%Time_Stamp(STOP);
Jim
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
9352 views
-
8 likes
-
-
