topic Re: Parse and reformat JSON data in Statistical Procedures

Parse and reformat JSON data

confooseddesi89 — Fri, 11 Aug 2023 22:53:45 GMT

Hello,

I have a .csv file with JSON data that looks as follows (sample of data below):

id	dep_date	dep_1_PA	date_2	dep_2_PA
1346	1/20/2019	['{"dep_count":4,"dep_duration":4,"happy_count":10,"qual":95,"end_datetime":"2019-01-25T06:44:30Z","date":"2019-01-25","mid":"2019-01-25T04:08:00Z","counter_report":0,"counter_nap":297,"counter_dep":15,"counter_to_eat":0}' '{"dep_count":0,"dep_duration":0,"happy_count":5,"qual":96,"end_datetime":"2019-01-26T07:15:30Z","date":"2019-01-26","mid":"2019-01-26T04:39:30Z","counter_report":0,"counter_nap":300,"counter_dep":13,"counter_to_eat":0}' '{"dep_count":1,"dep_duration":1,"happy_count":4,"qual":99,"end_datetime":"2019-01-27T06:46:00Z","date":"2019-01-27","mid":"2019-01-27T04:13:30Z","counter_report":6,"counter_nap":296,"counter_dep":2,"counter_to_eat":0}']	2/19/2019	['{"dep_count":0,"dep_duration":0,"happy_count":15,"qual":96,"end_datetime":"2019-02-04T12:25:30Z","date":"2019-02-04","mid":"2019-02-04T09:01:30Z","counter_report":0,"counter_nap":392,"counter_dep":17,"counter_to_eat":0} '{"dep_count":4,"dep_duration":7,"happy_count":13,"qual":93,"end_datetime":"2019-02-05T09:09:30Z","date":"2019-02-05","mid":"2019-02-05T06:13:00Z","counter_report":0,"counter_nap":327,"counter_dep":26,"counter_to_eat":0}' '{"dep_count":1,"dep_duration":3,"happy_count":12,"qual":95,"end_datetime":"2019-02-06T12:05:30Z","date":"2019-02-06","mid":"2019-02-06T08:12:00Z","counter_report":1,"counter_nap":444,"counter_dep":21,"counter_to_eat":0}']
1786	3/14/2019	['{"dep_count":2,"dep_duration":2,"happy_count":8,"qual":97,"end_datetime":"2019-03-10T05:39:00Z","date":"2019-03-10","mid":"2019-03-10T03:00:30Z","counter_report":0,"counter_nap":307,"counter_dep":9,"counter_to_eat":0}' '{"dep_count":0,"dep_duration":0,"happy_count":5,"qual":99,"end_datetime":"2019-03-11T04:38:30Z","date":"2019-03-11","mid":"2019-03-11T01:50:30Z","counter_report":0,"counter_nap":332,"counter_dep":5,"counter_to_eat":0}' '{"dep_count":1,"dep_duration":4,"happy_count":15,"qual":92,"end_datetime":"2019-03-12T05:49:00Z","date":"2019-03-12","mid":"2019-03-12T02:16:00Z","counter_report":0,"counter_nap":393,"counter_dep":34,"counter_to_eat":0}']	4/14/2019	[]

I need it to look like this:

ID	dep_date	dep_count	dep_duration	happy_count	qual	end_datetime	date	mid	counter_report	counter_nap	counter_dep	counter_to_eat
1346	1/20/2019	4	4	10	95	2019-01-25T06:44:30Z	1/25/2019	2019-01-25T04:08:00Z	0	297	15	0
1346	1/20/2019	0	0	5	96	2019-01-26T07:15:30Z	1/26/2019	2019-01-26T04:39:30Z	0	300	13	0
1346	1/20/2019	1	1	4	99	2019-01-27T06:46:00Z	1/27/2019	2019-01-27T04:13:30Z	6	296	2	0
1346	2/19/2019	0	0	15	96	2019-02-04T12:25:30Z	2/4/2019	2019-02-04T09:01:30Z	0	392	17	0
1346	2/19/2019	4	7	13	93	2019-02-05T09:09:30Z	2/5/2019	2019-02-05T06:13:00Z	0	327	26	0
1346	2/19/2019	1	3	12	95	2019-02-06T12:05:30Z	2/6/2019	2019-02-06T08:12:00Z	1	444	21	0
1786	3/14/2019	2	2	8	97	2019-03-10T05:39:00Z	3/10/2019	2019-03-10T03:00:30Z	0	307	9	0
1786	3/14/2019	0	0	5	99	2019-03-11T04:38:30Z	3/11/2019	2019-03-11T01:50:30Z	0	332	5	0
1786	3/14/2019	1	4	15	92	2019-03-12T05:49:00Z	3/12/2019	2019-03-12T02:16:00Z	0	393	34	0
1786	4/14/2019
1786	4/14/2019
1786	4/14/2019

Is there a way to parse this JSON data accordingly in SAS? I can't do it in Excel. Thanks.

Re: Parse and reformat JSON data

Patrick — Fri, 11 Aug 2023 23:07:56 GMT

Please attach a representative sample .csv. We need to understand in detail how your .csv looks like - especially where the line feeds are. Make sure the data you share really look like the source the SAS code needs to process (=avoid any intermediary step using Excel for providing the data).

Re: Parse and reformat JSON data

Tom — Fri, 11 Aug 2023 23:54:23 GMT

You have posted a listing, not a CSV file. A CSV files is a text file. A CSV file has only one line per observation.

The listing you posted appears to have line breaks in the middle of some of the cells. That will not work.

So first step is to make sure you actually have just one line of text in the CSV file.

Also it would be a lot easier to use something other than a COMMA is the separator in your text file since COMMA is a common character in JSON text. So perhaps use a pipe character as the delimiter. Or TAB if you never have to actually look at the file with an editor. TAB characters are normally invisible they make it hard to look at or modify files that have them.

The text you posted also does not look like JSON text to me. What is that extra single quote doing there in the beginning?

['{"dep_count":4,"dep_duration":4,"happy_count":10

Re: Parse and reformat JSON data

Tom — Sat, 12 Aug 2023 01:11:52 GMT

It is hard to tell from your post what the values in the DEP_1_PA and DEP_2_PA fields really have in them, but if they are consistently structed as those NAME:VALUE pairs then perhaps you can just use SCAN() to help you.

Try doing something like this to see what you get.

data tall;
  set have;
  length variable name $32 value $40 ;
  array x DEP_1_PA DEP_2_PA;
  do over x;
     variable = scan(vname(x),2,'_');
     do i=1 by 1 until(name=' ');
        name=dequote(scan(scan(x,i,"{{,'}]"),1,':'));
        value=scan(scan(x,i,"{{,'}]"),2,':');
        if name ne ' ' then output;
    end;
  end;
  drop DEP_1_PA DEP_2_PA;
run;

So you should now have a dataset like with ID, DEP_DATE, DATE_2, VARIABLE, NAME and VALUE.

You can then try re-ordering and using PROC TRANPOSE.

proc sort data=tall;
  by id dep_date date2 name variable ;
run;
proc transpose data=tall out=want;
  by id dep_date date2;
  id name variable;
  var value;
run;

Re: Parse and reformat JSON data

confooseddesi89 — Sat, 12 Aug 2023 22:03:00 GMT

I don't see how I can attach the .csv to a post, so I've uploaded it here

Re: Parse and reformat JSON data

confooseddesi89 — Sat, 12 Aug 2023 22:54:44 GMT

Hello,

Using the code you gave me, I get this error:

ERROR: The ID value "__1" occurs twice in the same BY group.
ERROR: The ID value "counter_dep1" occurs twice in the same BY group.
ERROR: The ID value "counter_dep1" occurs twice in the same BY group.
ERROR: The ID value "counter_dep2" occurs twice in the same BY group.
ERROR: The ID value "counter_dep2" occurs twice in the same BY group.
ERROR: The ID value "counter_nap1" occurs twice in the same BY group.
ERROR: The ID value "counter_nap1" occurs twice in the same BY group.
ERROR: The ID value "counter_nap2" occurs twice in the same BY group.
ERROR: The ID value "counter_nap2" occurs twice in the same BY group.
ERROR: The ID value "counter_report1" occurs twice in the same BY group.
ERROR: The ID value "counter_report1" occurs twice in the same BY group.
ERROR: The ID value "counter_report2" occurs twice in the same BY group.
ERROR: The ID value "counter_report2" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat1" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat1" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat2" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat2" occurs twice in the same BY group.
ERROR: The ID value "date1" occurs twice in the same BY group.
ERROR: The ID value "date1" occurs twice in the same BY group.
ERROR: The ID value "date2" occurs twice in the same BY group.
ERROR: The ID value "date2" occurs twice in the same BY group.
ERROR: The ID value "dep_count1" occurs twice in the same BY group.
ERROR: The ID value "dep_count1" occurs twice in the same BY group.
ERROR: The ID value "dep_count2" occurs twice in the same BY group.
ERROR: The ID value "dep_count2" occurs twice in the same BY group.
ERROR: The ID value "dep_duration1" occurs twice in the same BY group.
ERROR: The ID value "dep_duration1" occurs twice in the same BY group.
ERROR: The ID value "dep_duration2" occurs twice in the same BY group.
ERROR: The ID value "dep_duration2" occurs twice in the same BY group.
ERROR: The ID value "end_datetime1" occurs twice in the same BY group.
ERROR: The ID value "end_datetime1" occurs twice in the same BY group.
ERROR: The ID value "end_datetime2" occurs twice in the same BY group.
ERROR: The ID value "end_datetime2" occurs twice in the same BY group.
ERROR: The ID value "happy_count1" occurs twice in the same BY group.
ERROR: The ID value "happy_count1" occurs twice in the same BY group.
ERROR: The ID value "happy_count2" occurs twice in the same BY group.
ERROR: The ID value "happy_count2" occurs twice in the same BY group.
ERROR: The ID value "mid1" occurs twice in the same BY group.
ERROR: The ID value "mid1" occurs twice in the same BY group.
ERROR: The ID value "mid2" occurs twice in the same BY group.
ERROR: The ID value "mid2" occurs twice in the same BY group.
ERROR: The ID value "qual1" occurs twice in the same BY group.
ERROR: The ID value "qual1" occurs twice in the same BY group.
ERROR: The ID value "qual2" occurs twice in the same BY group.
ERROR: The ID value "qual2" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      id=1346 dep_date=01/20/2019 date_2=02/19/2019
ERROR: The ID value "__1" occurs twice in the same BY group.
ERROR: The ID value "counter_dep1" occurs twice in the same BY group.
ERROR: The ID value "counter_dep1" occurs twice in the same BY group.
ERROR: The ID value "counter_nap1" occurs twice in the same BY group.
ERROR: The ID value "counter_nap1" occurs twice in the same BY group.
ERROR: The ID value "counter_report1" occurs twice in the same BY group.
ERROR: The ID value "counter_report1" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat1" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat1" occurs twice in the same BY group.
ERROR: The ID value "date1" occurs twice in the same BY group.
ERROR: The ID value "date1" occurs twice in the same BY group.
ERROR: The ID value "dep_count1" occurs twice in the same BY group.
ERROR: The ID value "dep_count1" occurs twice in the same BY group.
ERROR: The ID value "dep_duration1" occurs twice in the same BY group.
ERROR: The ID value "dep_duration1" occurs twice in the same BY group.
ERROR: The ID value "end_datetime1" occurs twice in the same BY group.
ERROR: The ID value "end_datetime1" occurs twice in the same BY group.
ERROR: The ID value "happy_count1" occurs twice in the same BY group.
ERROR: The ID value "happy_count1" occurs twice in the same BY group.
ERROR: The ID value "mid1" occurs twice in the same BY group.
ERROR: The ID value "mid1" occurs twice in the same BY group.
ERROR: The ID value "qual1" occurs twice in the same BY group.
ERROR: The ID value "qual1" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      id=1786 dep_date=03/14/2019 date_2=04/14/2019
ERROR: All BY groups were bad.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 109 observations read from the data set WORK.TALL.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Also, the "tall" dataset looks like below:

id	dep_date	date_2	variable	name	value	i
1346	1/20/2019	2/19/2019	1			13
1346	1/20/2019	2/19/2019	1			25
1346	1/20/2019	2/19/2019	2			25
1346	1/20/2019	2/19/2019	2			13
1346	1/20/2019	2/19/2019	1	[		1
1346	1/20/2019	2/19/2019	2	[		1
1346	1/20/2019	2/19/2019	1	counter_dep	15	11
1346	1/20/2019	2/19/2019	1	counter_dep	13	23
1346	1/20/2019	2/19/2019	1	counter_dep	2	35
1346	1/20/2019	2/19/2019	2	counter_dep	17	11
1346	1/20/2019	2/19/2019	2	counter_dep	26	23
1346	1/20/2019	2/19/2019	2	counter_dep	21	35
1346	1/20/2019	2/19/2019	1	counter_nap	297	10
1346	1/20/2019	2/19/2019	1	counter_nap	300	22
1346	1/20/2019	2/19/2019	1	counter_nap	296	34
1346	1/20/2019	2/19/2019	2	counter_nap	392	10
1346	1/20/2019	2/19/2019	2	counter_nap	327	22
1346	1/20/2019	2/19/2019	2	counter_nap	444	34
1346	1/20/2019	2/19/2019	1	counter_report	0	9
1346	1/20/2019	2/19/2019	1	counter_report	0	21
1346	1/20/2019	2/19/2019	1	counter_report	6	33
1346	1/20/2019	2/19/2019	2	counter_report	0	9
1346	1/20/2019	2/19/2019	2	counter_report	0	21
1346	1/20/2019	2/19/2019	2	counter_report	1	33
1346	1/20/2019	2/19/2019	1	counter_to_eat	0	12
1346	1/20/2019	2/19/2019	1	counter_to_eat	0	24
1346	1/20/2019	2/19/2019	1	counter_to_eat	0	36
1346	1/20/2019	2/19/2019	2	counter_to_eat	0	12
1346	1/20/2019	2/19/2019	2	counter_to_eat	0	24
1346	1/20/2019	2/19/2019	2	counter_to_eat	0	36
1346	1/20/2019	2/19/2019	1	date	"2019-01-25"	7
1346	1/20/2019	2/19/2019	1	date	"2019-01-26"	19
1346	1/20/2019	2/19/2019	1	date	"2019-01-27"	31
1346	1/20/2019	2/19/2019	2	date	"2019-02-04"	7
1346	1/20/2019	2/19/2019	2	date	"2019-02-05"	19
1346	1/20/2019	2/19/2019	2	date	"2019-02-06"	31
1346	1/20/2019	2/19/2019	1	dep_count	4	2
1346	1/20/2019	2/19/2019	1	dep_count	0	14
1346	1/20/2019	2/19/2019	1	dep_count	1	26
1346	1/20/2019	2/19/2019	2	dep_count	0	2
1346	1/20/2019	2/19/2019	2	dep_count	4	14
1346	1/20/2019	2/19/2019	2	dep_count	1	26
1346	1/20/2019	2/19/2019	1	dep_duration	4	3
1346	1/20/2019	2/19/2019	1	dep_duration	0	15
1346	1/20/2019	2/19/2019	1	dep_duration	1	27
1346	1/20/2019	2/19/2019	2	dep_duration	0	3
1346	1/20/2019	2/19/2019	2	dep_duration	7	15
1346	1/20/2019	2/19/2019	2	dep_duration	3	27
1346	1/20/2019	2/19/2019	1	end_datetime	"2019-01-25T06	6
1346	1/20/2019	2/19/2019	1	end_datetime	"2019-01-26T07	18
1346	1/20/2019	2/19/2019	1	end_datetime	"2019-01-27T06	30
1346	1/20/2019	2/19/2019	2	end_datetime	"2019-02-04T12	6
1346	1/20/2019	2/19/2019	2	end_datetime	"2019-02-05T09	18
1346	1/20/2019	2/19/2019	2	end_datetime	"2019-02-06T12	30
1346	1/20/2019	2/19/2019	1	happy_count	10	4
1346	1/20/2019	2/19/2019	1	happy_count	5	16
1346	1/20/2019	2/19/2019	1	happy_count	4	28
1346	1/20/2019	2/19/2019	2	happy_count	15	4
1346	1/20/2019	2/19/2019	2	happy_count	13	16
1346	1/20/2019	2/19/2019	2	happy_count	12	28
1346	1/20/2019	2/19/2019	1	mid	"2019-01-25T04	8
1346	1/20/2019	2/19/2019	1	mid	"2019-01-26T04	20
1346	1/20/2019	2/19/2019	1	mid	"2019-01-27T04	32
1346	1/20/2019	2/19/2019	2	mid	"2019-02-04T09	8
1346	1/20/2019	2/19/2019	2	mid	"2019-02-05T06	20
1346	1/20/2019	2/19/2019	2	mid	"2019-02-06T08	32
1346	1/20/2019	2/19/2019	1	qual	95	5
1346	1/20/2019	2/19/2019	1	qual	96	17
1346	1/20/2019	2/19/2019	1	qual	99	29
1346	1/20/2019	2/19/2019	2	qual	96	5
1346	1/20/2019	2/19/2019	2	qual	93	17
1346	1/20/2019	2/19/2019	2	qual	95	29
1786	3/14/2019	4/14/2019	1			13
1786	3/14/2019	4/14/2019	1			25
1786	3/14/2019	4/14/2019	1	[		1
1786	3/14/2019	4/14/2019	2	[		1
1786	3/14/2019	4/14/2019	1	counter_dep	9	11
1786	3/14/2019	4/14/2019	1	counter_dep	5	23
1786	3/14/2019	4/14/2019	1	counter_dep	34	35
1786	3/14/2019	4/14/2019	1	counter_nap	307	10
1786	3/14/2019	4/14/2019	1	counter_nap	332	22
1786	3/14/2019	4/14/2019	1	counter_nap	393	34
1786	3/14/2019	4/14/2019	1	counter_report	0	9
1786	3/14/2019	4/14/2019	1	counter_report	0	21
1786	3/14/2019	4/14/2019	1	counter_report	0	33
1786	3/14/2019	4/14/2019	1	counter_to_eat	0	12
1786	3/14/2019	4/14/2019	1	counter_to_eat	0	24
1786	3/14/2019	4/14/2019	1	counter_to_eat	0	36
1786	3/14/2019	4/14/2019	1	date	"2019-03-10"	7
1786	3/14/2019	4/14/2019	1	date	"2019-03-11"	19
1786	3/14/2019	4/14/2019	1	date	"2019-03-12"	31
1786	3/14/2019	4/14/2019	1	dep_count	2	2
1786	3/14/2019	4/14/2019	1	dep_count	0	14
1786	3/14/2019	4/14/2019	1	dep_count	1	26
1786	3/14/2019	4/14/2019	1	dep_duration	2	3
1786	3/14/2019	4/14/2019	1	dep_duration	0	15
1786	3/14/2019	4/14/2019	1	dep_duration	4	27
1786	3/14/2019	4/14/2019	1	end_datetime	"2019-03-10T05	6
1786	3/14/2019	4/14/2019	1	end_datetime	"2019-03-11T04	18
1786	3/14/2019	4/14/2019	1	end_datetime	"2019-03-12T05	30
1786	3/14/2019	4/14/2019	1	happy_count	8	4
1786	3/14/2019	4/14/2019	1	happy_count	5	16
1786	3/14/2019	4/14/2019	1	happy_count	15	28
1786	3/14/2019	4/14/2019	1	mid	"2019-03-10T03	8
1786	3/14/2019	4/14/2019	1	mid	"2019-03-11T01	20
1786	3/14/2019	4/14/2019	1	mid	"2019-03-12T02	32
1786	3/14/2019	4/14/2019	1	qual	97	5
1786	3/14/2019	4/14/2019	1	qual	99	17
1786	3/14/2019	4/14/2019	1	qual	92	29

The parsing by dep_date / date_2 is incorrect. For example, the values 15, 13, and 2 in the "value" column belong to date 1/20/2019, whereas the values 17, 26, and 21 belong to date 2/19/2019. None of these value belong to both of these dates (2nd and third columns should be collapsed). I used the code below:

data tall; set  tall; if Variable=2 then dep_date=date_2;drop date_2;run;

To get this:

id	dep_date	variable	name	value	i
1346	1/20/2019	1	[		1
1346	1/20/2019	1	dep_count	4	2
1346	1/20/2019	1	dep_duration	4	3
1346	1/20/2019	1	happy_count	10	4
1346	1/20/2019	1	qual	95	5
1346	1/20/2019	1	end_datetime	"2019-01-25T06	6
1346	1/20/2019	1	date	"2019-01-25"	7
1346	1/20/2019	1	mid	"2019-01-25T04	8
1346	1/20/2019	1	counter_report	0	9
1346	1/20/2019	1	counter_nap	297	10
1346	1/20/2019	1	counter_dep	15	11
1346	1/20/2019	1	counter_to_eat	0	12
1346	1/20/2019	1			13
1346	1/20/2019	1	dep_count	0	14
1346	1/20/2019	1	dep_duration	0	15
1346	1/20/2019	1	happy_count	5	16
1346	1/20/2019	1	qual	96	17
1346	1/20/2019	1	end_datetime	"2019-01-26T07	18
1346	1/20/2019	1	date	"2019-01-26"	19
1346	1/20/2019	1	mid	"2019-01-26T04	20
1346	1/20/2019	1	counter_report	0	21
1346	1/20/2019	1	counter_nap	300	22
1346	1/20/2019	1	counter_dep	13	23
1346	1/20/2019	1	counter_to_eat	0	24
1346	1/20/2019	1			25
1346	1/20/2019	1	dep_count	1	26
1346	1/20/2019	1	dep_duration	1	27
1346	1/20/2019	1	happy_count	4	28
1346	1/20/2019	1	qual	99	29
1346	1/20/2019	1	end_datetime	"2019-01-27T06	30
1346	1/20/2019	1	date	"2019-01-27"	31
1346	1/20/2019	1	mid	"2019-01-27T04	32
1346	1/20/2019	1	counter_report	6	33
1346	1/20/2019	1	counter_nap	296	34
1346	1/20/2019	1	counter_dep	2	35
1346	1/20/2019	1	counter_to_eat	0	36
1346	2/19/2019	2	[		1
1346	2/19/2019	2	dep_count	0	2
1346	2/19/2019	2	dep_duration	0	3
1346	2/19/2019	2	happy_count	15	4
1346	2/19/2019	2	qual	96	5
1346	2/19/2019	2	end_datetime	"2019-02-04T12	6
1346	2/19/2019	2	date	"2019-02-04"	7
1346	2/19/2019	2	mid	"2019-02-04T09	8
1346	2/19/2019	2	counter_report	0	9
1346	2/19/2019	2	counter_nap	392	10
1346	2/19/2019	2	counter_dep	17	11
1346	2/19/2019	2	counter_to_eat	0	12
1346	2/19/2019	2			13
1346	2/19/2019	2	dep_count	4	14
1346	2/19/2019	2	dep_duration	7	15
1346	2/19/2019	2	happy_count	13	16
1346	2/19/2019	2	qual	93	17
1346	2/19/2019	2	end_datetime	"2019-02-05T09	18
1346	2/19/2019	2	date	"2019-02-05"	19
1346	2/19/2019	2	mid	"2019-02-05T06	20
1346	2/19/2019	2	counter_report	0	21
1346	2/19/2019	2	counter_nap	327	22
1346	2/19/2019	2	counter_dep	26	23
1346	2/19/2019	2	counter_to_eat	0	24
1346	2/19/2019	2			25
1346	2/19/2019	2	dep_count	1	26
1346	2/19/2019	2	dep_duration	3	27
1346	2/19/2019	2	happy_count	12	28
1346	2/19/2019	2	qual	95	29
1346	2/19/2019	2	end_datetime	"2019-02-06T12	30
1346	2/19/2019	2	date	"2019-02-06"	31
1346	2/19/2019	2	mid	"2019-02-06T08	32
1346	2/19/2019	2	counter_report	1	33
1346	2/19/2019	2	counter_nap	444	34
1346	2/19/2019	2	counter_dep	21	35
1346	2/19/2019	2	counter_to_eat	0	36
1786	3/14/2019	1	[		1
1786	3/14/2019	1	dep_count	2	2
1786	3/14/2019	1	dep_duration	2	3
1786	3/14/2019	1	happy_count	8	4
1786	3/14/2019	1	qual	97	5
1786	3/14/2019	1	end_datetime	"2019-03-10T05	6
1786	3/14/2019	1	date	"2019-03-10"	7
1786	3/14/2019	1	mid	"2019-03-10T03	8
1786	3/14/2019	1	counter_report	0	9
1786	3/14/2019	1	counter_nap	307	10
1786	3/14/2019	1	counter_dep	9	11
1786	3/14/2019	1	counter_to_eat	0	12
1786	3/14/2019	1			13
1786	3/14/2019	1	dep_count	0	14
1786	3/14/2019	1	dep_duration	0	15
1786	3/14/2019	1	happy_count	5	16
1786	3/14/2019	1	qual	99	17
1786	3/14/2019	1	end_datetime	"2019-03-11T04	18
1786	3/14/2019	1	date	"2019-03-11"	19
1786	3/14/2019	1	mid	"2019-03-11T01	20
1786	3/14/2019	1	counter_report	0	21
1786	3/14/2019	1	counter_nap	332	22
1786	3/14/2019	1	counter_dep	5	23
1786	3/14/2019	1	counter_to_eat	0	24
1786	3/14/2019	1			25
1786	3/14/2019	1	dep_count	1	26
1786	3/14/2019	1	dep_duration	4	27
1786	3/14/2019	1	happy_count	15	28
1786	3/14/2019	1	qual	92	29
1786	3/14/2019	1	end_datetime	"2019-03-12T05	30
1786	3/14/2019	1	date	"2019-03-12"	31
1786	3/14/2019	1	mid	"2019-03-12T02	32
1786	3/14/2019	1	counter_report	0	33
1786	3/14/2019	1	counter_nap	393	34
1786	3/14/2019	1	counter_dep	34	35
1786	3/14/2019	1	counter_to_eat	0	36
1786	4/14/2019	2	[		1

I then used the "proc transpose" code you gave me, while deleting "date2" and "variable" (which should not be in the ID statement, as the data should remain by the dep_date/variable) and I still got the error "the ID value [name] occurs twice in the same BY group." See below:

ERROR: The ID value "__" occurs twice in the same BY group.
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      id=1346 dep_date=01/20/2019
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      id=1346 dep_date=02/19/2019
ERROR: The ID value "__" occurs twice in the same BY group.
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_dep" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_nap" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_report" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "counter_to_eat" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "date" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_count" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "dep_duration" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "end_datetime" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "happy_count" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "mid" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
ERROR: The ID value "qual" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      id=1786 dep_date=03/14/2019
WARNING: 3 BY groups omitted due to earlier errors.
NOTE: There were 109 observations read from the data set WORK.TALL.
NOTE: The data set WORK.WANT has 1 observations and 17 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

I understand why I'm getting this error; I therefore created a counter by id, dep_date, and name, "day_count", and added "day_count" as a BY group in the PROC TRANSPOSE, which solved the issue. My data now look like this:

id	dep_date	day_count	_NAME_	__	_	counter_dep	counter_nap	counter_report	counter_to_eat	date	dep_count	dep_duration	end_datetime	happy_count	mid	qual	___
1346	1/20/2019	1	value			15	297	0	0	"2019-01-25"	4	4	"2019-01-25T06	10	"2019-01-25T04	95
1346	1/20/2019	2	value			13	300	0	0	"2019-01-26"	0	0	"2019-01-26T07	5	"2019-01-26T04	96
1346	1/20/2019	3	value			2	296	6	0	"2019-01-27"	1	1	"2019-01-27T06	4	"2019-01-27T04	99
1346	2/19/2019	1	value			17	392	0	0	"2019-02-04"	0	0	"2019-02-04T12	15	"2019-02-04T09	96
1346	2/19/2019	2	value			26	327	0	0	"2019-02-05"	4	7	"2019-02-05T09	13	"2019-02-05T06	93
1346	2/19/2019	3	value			21	444	1	0	"2019-02-06"	1	3	"2019-02-06T12	12	"2019-02-06T08	95
1786	3/14/2019	1	value			9	307	0	0	"2019-03-10"	2	2	"2019-03-10T05	8	"2019-03-10T03	97
1786	3/14/2019	2	value			5	332	0	0	"2019-03-11"	0	0	"2019-03-11T04	5	"2019-03-11T01	99
1786	3/14/2019	3	value			34	393	0	0	"2019-03-12"	1	4	"2019-03-12T05	15	"2019-03-12T02	92
1786	4/14/2019	1	value

However:

1) The value is cut off for end_datetime and mid (e.g., "2019-01-25T06 should be 2019-01-25T06:44:30Z). This problem was evident in the original "tall" dataset, as you can see in the first table I posted.

1) I need to strip the quotation marks off of date (which should be in date format), end_datetime, and mid (which should both be in datetime format).

Thanks.

Re: Parse and reformat JSON data

Patrick — Sun, 13 Aug 2023 02:07:39 GMT

@confooseddesi89 wrote:

I don't see how I can attach the .csv to a post, so I've uploaded it here

@confooseddesi89 Just drag&drop. I've done it here now because many people won't download from some link.

The following code won't return the final result you're likely after but should take you one step further. Please don't just state what's still missing but just where you need further help with.

One of the challenges with your data: The json in the data needs some clean-up to use with a json engine. The question is where all this additional quoting got added.

Could it be that the .csv you've shared is some export from a database where the json string is stored in a CLOB field? Would you in the end directly access the database? And if so: Which database?

%let source_path=C:\temp;
data step1;
  infile "&source_path\dep_PA to paste sample v3 for sas forum.csv" 
    firstobs=2
    lrecl=10000
    encoding="utf-8"
    termstr=crlf
    dlm=','
    truncover
    dsd 
    ;    

  length id dep 8;
  format dep_date date9.;

  input id dep_date:mmddyy10. dep_PA:$2000. @;
  dep=1;
  dep_pa=transtrn(dep_pa,"['",'[');
  dep_pa=transtrn(dep_pa,"']",']');
  dep_pa=transtrn(dep_pa,'""','"');
  output;
  input dep_date:mmddyy10. dep_PA:$2000.;
  dep=2;
  dep_pa=transtrn(dep_pa,"['",'[');
  dep_pa=transtrn(dep_pa,"']",']');
  dep_pa=transtrn(dep_pa,'""','"');
  output;

run;

data step2;
  set step1;
  length name $32 value $40 ;

  do i=1 by 1 until(name=' ');
    name=dequote(scan(scan(dep_pa,i,"{{,'}]"),1,':'));
    value=scan(scan(dep_pa,i,"{{,'}]"),2,':');
    if name ne ' ' then output;
  end;
run;

Re: Parse and reformat JSON data

Tom — Sun, 13 Aug 2023 04:25:35 GMT

Looks like you are almost there.

You appear to be using too short a variable when scanning. or perhaps your JSON text is just truncating when you read it into SAS?

You appear to have used the wrong delimiters in the SCAN() function call if it is use the slash in the date strings as a delimiter.

You appear to have multiple values for the same NAME. What do you want to do with those? You could add a new counter variable. Then you could either add the new variable to the BY group or the ID variable list so that you have distinct names.

Re: Parse and reformat JSON data

Tom — Sun, 13 Aug 2023 05:29:49 GMT

Someone above posted an actual file. So let's use that file.

The first thing that needs to be fixed it to remove the END OF LINE characters from some of the values so that each observation is only ONE LINE in the CSV file. You can use this macro: https://github.com/sasutils/macros/blob/master/replace_crlf.sas

Let's replace the LINE FEED characters with a PIPE character so we know where the line breaks used to be.

filename csv "c:\downloads\dep_PA to paste sample v3 for sas forum.csv";
filename csvfix temp;
%replace_crlf(csv encoding=any,csvfix,cr=,lf='|');

Now we can read the CSV file into an actual SAS dataset.

data have;
  infile csvfix dsd truncover firstobs=2 ;
  input id $ dep_date :mmddyy. dep_1_PA :$1500. date_2 :mmddyy. dep_2_PA :$1500.;
  format dep_date date_2 yymmdd10.;
run;

Now let's parse the strings into records and then into terms and then into NAME/VALUE pairs.

data tall;
  set have;
  array x DEP_1_PA DEP_2_PA;
  length col line row 8 name $32 value $200 term $300 record $1500;
  do col=1 to dim(x);
   do line=1 by 1 until(record=' ');
     record=scan(x[col],line,'|');
     do row=1 by 1 until(term=' ');
        term=scan(record,row,"[{,'}]");
        term=translate(term,' ','A0'x);
        name=dequote(scan(term,1,':','q'));
        value=dequote(scan(term,2,':','q'));     
        if term ne ' ' then output;
    end;
   end;
  end;
  drop DEP_1_PA DEP_2_PA term record;
run;

Now we can transpose

proc transpose data=tall out=want(drop=_name_);
  by id dep_date date_2 col line;
  id name ;
  var value;
run;

Note that everything is a character string, but most of those variables look like numbers to me.

You could write the file back out into an actual CSV file and let PROC IMPORT try to read it in and it will do a pretty good job with this small sample.

filename newcsv temp;
proc export data=want file=newcsv dbms=csv;
run;

proc import file=newcsv dbms=csv out=want2 ;
run;

                  Variables in Creation Order

 #    Variable          Type    Len    Format        Informat

 1    id                Num       8    BEST12.       BEST32.
 2    dep_date          Num       8    YYMMDD10.     YYMMDD10.
 3    date_2            Num       8    YYMMDD10.     YYMMDD10.
 4    col               Num       8    BEST12.       BEST32.
 5    line              Num       8    BEST12.       BEST32.
 6    dep_count         Num       8    BEST12.       BEST32.
 7    dep_duration      Num       8    BEST12.       BEST32.
 8    happy_count       Num       8    BEST12.       BEST32.
 9    qual              Num       8    BEST12.       BEST32.
10    end_datetime      Num       8    B8601DZ35.    B8601DZ35.
11    date              Num       8    YYMMDD10.     YYMMDD10.
12    mid               Num       8    B8601DZ35.    B8601DZ35.
13    counter_report    Num       8    BEST12.       BEST32.
14    counter_nap       Num       8    BEST12.       BEST32.
15    counter_dep       Num       8    BEST12.       BEST32.
16    counter_to_eat    Num       8    BEST12.       BEST32.

Re: Parse and reformat JSON data

confooseddesi89 — Mon, 14 Aug 2023 18:56:38 GMT

Hi,

This is nearly perfect, but:

1.) One line of data is missing; where end_datetime=2019-02-05T09:09:30Z (should be between rows 4 and 5). See below:

Obs	id	dep_date	col	line	dep_count	dep_duration	happy_count	qual	end_datetime	date	mid	counter_report	counter_nap	counter_dep
1	1346	2019-01-20	1	1	4	4	10	95	25JAN19:06:44:30	2019-01-25	25JAN19:04:08:00	0	297	15
2	1346	2019-01-20	1	2	0	0	5	96	26JAN19:07:15:30	2019-01-26	26JAN19:04:39:30	0	300	13
3	1346	2019-01-20	1	3	1	1	4	99	27JAN19:06:46:00	2019-01-27	27JAN19:04:13:30	6	296	2
4	1346	2019-02-19	2	1	0	0	15	96	04FEB19:12:25:30	2019-02-04	04FEB19:09:01:30	0	392	17
5	1346	2019-02-19	2	3	1	3	12	95	06FEB19:12:05:30	2019-02-06	06FEB19:08:12:00	1	444	21
6	1786	2019-03-14	1	1	2	2	8	97	10MAR19:05:39:00	2019-03-10	10MAR19:03:00:30	0	307	9
7	1786	2019-03-14	1	2	0	0	5	99	11MAR19:04:38:30	2019-03-11	11MAR19:01:50:30	0	332	5
8	1786	2019-03-14	1	3	1	4	15	92	12MAR19:05:49:00	2019-03-12	12MAR19:02:16:00	0	393	34

The line of data should look like this:

ID	dep_date	col	line	dep_count	dep_duration	happy_count	qual	end_datetime	date	mid	counter_report	counter_nap	counter_dep	counter_to_eat
1346	2019-02-19	2	2	4	7	13	93	05FEB19:09:09:30Z	2019-02-05	05FEB19:06:13:00Z	0	327	26	0

I've discovered the data gets lost somewhere when moving from "have" to "tall" (the code below):

data tall;
  set have;
  array x DEP_1_PA DEP_2_PA;
  length col line row 8 name $32 value $200 term $300 record $1500;
  do col=1 to dim(x);
   do line=1 by 1 until(record=' ');
     record=scan(x[col],line,'|');
     do row=1 by 1 until(term=' ');
        term=scan(record,row,"[{,'}]");
        term=translate(term,' ','A0'x);
        name=dequote(scan(term,1,':','q'));
        value=dequote(scan(term,2,':','q'));     
        if term ne ' ' then output;
    end;
   end;
  end;
  drop DEP_1_PA DEP_2_PA term record;
run;

2) The issue with the duplicate dates ("date_2") is still present; e.g., where dates are 2019-01-25, 2019-01-26, and 2019-01-27, the dep_date should be 1/20/2019, whereas where dates are 2019-02-04 and 2019-02-06, dep_date should be 2/19/2019. None of these value belong to both of these dates (dep_date and date_2 should be collapsed). I used the code below:

data want2; set want2; if col=2 then dep_date=date_2;drop date_2;run;

However, is there a better way to do this - to not create "date_2" to begin with? (My larger dataset also has more than 2 "dep_dates" per person, which will make correcting the issue as with the above "if" code more tedious.)

Thanks.

Re: Parse and reformat JSON data

Tom — Mon, 14 Aug 2023 19:10:07 GMT

Look at the formatting of LINE 2 for that source observation.

Remove the DROP statement and you should see it in RECORD.

See if you can figure out why it is different than line 1 and line 3 for that observation.

Do perhaps some of the values not have NAME:VALUE pairs? Does it have some extra strange characters that perhaps should be used as delimiters in the second scan() to look for the pairs?

Re: Parse and reformat JSON data

Tom — Mon, 14 Aug 2023 19:28:27 GMT

Looks like there are more non-breaking spaces inserted. You could perhaps include those in the second SCAN() function. You could probably remove the line that replaces them with actual spaces since you are now using them as delimiter.

data tall;
  set have;
  array x DEP_1_PA DEP_2_PA;
  length col line row 8 name $32 value $200 term $300 record $1500;
  do col=1 to dim(x);
   do line=1 by 1 until(record=' ');
     record=scan(x[col],line,'|');
     do row=1 by 1 until(term=' ');
        term=scan(record,row,"[{,'}]"||'A0'x);
        term=translate(term,' ','A0'x);
        name=dequote(scan(term,1,':','q'));
        value=dequote(scan(term,2,':','q'));     
        if term ne ' ' then output;
    end;
   end;
  end;
  drop DEP_1_PA DEP_2_PA term record;
run;

3392  data _null_;
3393    set tall;
3394    where id='1346' and col=2 and line=2;
3395    put (_all_) (=) ;
3396  run;

id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=1 name=dep_count value=4
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=2 name=dep_duration value=7
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=3 name=happy_count value=13
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=4 name=qual value=93
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=5 name=end_datetime value=2019-02-05T09:09:30Z
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=6 name=date value=2019-02-05
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=7 name=mid value=2019-02-05T06:13:00Z
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=8 name=counter_report value=0
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=9 name=counter_nap value=327
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=10 name=counter_dep value=26
id=1346 dep_date=2019-01-20 date_2=2019-02-19 col=2 line=2 row=11 name=counter_to_eat value=0
NOTE: There were 11 observations read from the data set WORK.TALL.
      WHERE (id='1346') and (col=2) and (line=2);
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Parse and reformat JSON data

Tom — Mon, 14 Aug 2023 19:24:17 GMT

What is the meaning of the two date columns in the CSV file?

How do those dates relate to the dates in the JSON text?

Why not just use a different name for those two dates that does not conflict with the names in the JSON text?

Re: Parse and reformat JSON data

confooseddesi89 — Mon, 14 Aug 2023 19:37:55 GMT

Thanks, the new code worked.

The dep_date is a reference date. It's actually not that important - but I'm not sure what you mean by " use a different name." The dep_date and date_2 names are not used for any of the JSON data.

Re: Parse and reformat JSON data

Tom — Mon, 14 Aug 2023 21:07:58 GMT

The code I posted works is it takes that one observation and splits into separate observations for reach JSON string VARIABLE and each LINE in within that variable.

The only way it could cause name conflicts is that you have the same name in the same line or the name conflicts with one of the grouping variables. If you have same the NAME in the same LINE of the same VARIABLE then what does that mean? And what do you want to DO about it?

(Note this is why JSON is a terrible format for storing relational data.)

Re: Parse and reformat JSON data

confooseddesi89 — Wed, 16 Aug 2023 02:55:11 GMT

I agree that JSON is terrible and I was unfortunately forced to use it.

My apologies, earlier I shared with you a sample of my data; I tried your code with my much larger dataset and ran into some errors. I was trying to make the task easier for any responders by sharing only a sample, but unfortunately now the code is not working on my actual dataset (45 columns, 847 rows, and 20 variables in each "" JSON segment). (Getting a code for a sample and then applying it to a larger dataset has always worked before on this forum for me).

I used the replace_crlf on my larger dataset (which has the same structure - I tried to attach the file here but it exceeds the size limit; it's uploaded here) and my output csv looked like this below, where everything was in one column separated by commas, and each record was cut with a carriage return in the wrong place (only displaying first 2 rows since it's so large):

id,anx_1_date,anx_1_PA,dep_1_date,dep_1_PA,dep_2_date,dep_2_PA,dep_3_date,dep_3_PA,dep_4_date,dep_4_PA,dep_5_date,dep_5_PA,dep_6_date,dep_6_PA,dep_7_date,dep_7_PA,dep_8_date,dep_8_PA,dep_9_date,dep_9_PA,dep_10_date,dep_10_PA,dep_11_date,dep_11_PA,dep_12_date,dep_12_PA,dep_13_date,dep_13_PA,dep_14_date,dep_14_PA,dep_15_date,dep_15_PA,dep_16_date,dep_16_PA,dep_17_date,dep_17_PA,dep_18_date,dep_18_PA,dep_19_date,dep_19_PA,dep_20_date,dep_20_PA,dep_21_date,dep_21_PA,,,,,

1346,1/19/2019,"['{""dep_count"":2,""dep_duration"":2,""happy_count"":12,""qual"":90,""end_datetime"":""2019-01-15T10:15:00Z"",""date"":""2019-01-15"",""mid"":""2019-01-15T07:35:00Z"",""counter_report"":0,""counter_nap"":289,""counter_dep"":32,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":30,""start_datetime"":""2019-01-15T04:54:00Z"",""act_deep"":20,""act_light"":231,""act_rem"":26,""act_wake"":44,""time_sedentary"":321,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":10,""qual"":97,""end_datetime"":""2019-01-16T11:27:30Z"",""date"":""2019-01-16"",""mid"":""2019-01-16T07:42:30Z"",""counter_report"":3,""counter_nap"":433,""counter_dep"":14,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":16,""start_datetime"":""2019-01-16T03:57:30Z"",""act_deep"":100,""act_light"":233,""act_rem"":77,""act_wake"":40,""time_sedentary"":450,""id"":1346}'| '{""dep_count"":4,""dep_duration"":9,""happy_count"":15,""qual"":89,""end_datetime"":""2019-01-18T13:35:30Z"",""date"":""2019-01-18"",""mid"":""2019-01-18T10:32:00Z"",""counter_report"":0,""counter_nap"":325,""counter_dep"":42,""counter_to_eat"":0,""stress_count"":11,""stress_duration"":33,""start_datetime"":""2019-01-18T07:28:00Z"",""act_deep"":28,""act_light"":261,""act_rem"":18,""act_wake"":60,""time_sedentary"":367,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":15,""qual"":96,""end_datetime"":""2019-01-19T13:42:30Z"",""date"":""2019-01-19"",""mid"":""2019-01-19T09:57:00Z"",""counter_report"":0,""counter_nap"":432,""counter_dep"":18,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":16,""start_datetime"":""2019-01-19T06:12:00Z"",""act_deep"":87,""act_light"":229,""act_rem"":38,""act_wake"":96,""time_sedentary"":450,""id"":1346}'| '{""dep_count"":7,""dep_duration"":9,""happy_count"":20,""qual"":92,""end_datetime"":""2019-01-20T17:58:00Z"",""date"":""2019-01-20"",""mid"":""2019-01-20T13:21:00Z"",""counter_report"":0,""counter_nap"":512,""counter_dep"":43,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":34,""start_datetime"":""2019-01-20T08:43:00Z"",""act_deep"":95,""act_light"":290,""act_rem"":83,""act_wake"":87,""time_sedentary"":555,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":23,""qual"":93,""end_datetime"":""2019-01-21T16:33:30Z"",""date"":""2019-01-21"",""mid"":""2019-01-21T11:11:00Z"",""counter_report"":1,""counter_nap"":597,""counter_dep"":46,""counter_to_eat"":0,""stress_count"":23,""stress_duration"":47,""start_datetime"":""2019-01-21T05:49:00Z"",""act_deep"":110,""act_light"":373,""act_rem"":64,""act_wake"":97,""time_sedentary"":644,""id"":1346}'| '{""dep_count"":2,""dep_duration"":5,""happy_count"":18,""qual"":94,""end_datetime"":""2019-01-22T13:24:30Z"",""date"":""2019-01-22"",""mid"":""2019-01-22T09:09:00Z"",""counter_report"":0,""counter_nap"":480,""counter_dep"":30,""counter_to_eat"":0,""stress_count"":16,""stress_duration"":25,""start_datetime"":""2019-01-22T04:54:00Z"",""act_deep"":90,""act_light"":302,""act_rem"":69,""act_wake"":49,""time_sedentary"":510,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":9,""qual"":96,""end_datetime"":""2019-01-23T13:13:00Z"",""date"":""2019-01-23"",""mid"":""2019-01-23T10:27:30Z"",""counter_report"":0,""counter_nap"":317,""counter_dep"":13,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":13,""start_datetime"":""2019-01-23T07:42:30Z"",""act_deep"":58,""act_light"":196,""act_rem"":21,""act_wake"":55,""time_sedentary"":330,""id"":1346}'| '{""dep_count"":1,""dep_duration"":2,""happy_count"":20,""qual"":92,""end_datetime"":""2019-01-24T07:02:00Z"",""date"":""2019-01-24"",""mid"":""2019-01-24T03:01:30Z"",""counter_report"":2,""counter_nap"":443,""counter_dep"":36,""counter_to_eat"":0,""stress_count"":19,""stress_duration"":36,""start_datetime"":""2019-01-23T23:00:30Z"",""act_deep"":84,""act_light"":233,""act_rem"":97,""act_wake"":67,""time_sedentary"":481,""id"":1346}'| '{""dep_count"":4,""dep_duration"":4,""happy_count"":19,""qual"":88,""end_datetime"":""2019-01-25T11:48:00Z"",""date"":""2019-01-25"",""mid"":""2019-01-25T08:43:00Z"",""counter_report"":0,""counter_nap"":326,""counter_dep"":44,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":40,""start_datetime"":""2019-01-25T05:38:00Z"",""act_deep"":55,""act_light"":212,""act_rem"":6,""act_wake"":97,""time_sedentary"":370,""id"":1346}'| '{""dep_count"":3,""dep_duration"":3,""happy_count"":17,""qual"":92,""end_datetime"":""2019-01-26T14:12:30Z"",""date"":""2019-01-26"",""mid"":""2019-01-26T10:18:00Z"",""counter_report"":0,""counter_nap"":430,""counter_dep"":39,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":36,""start_datetime"":""2019-01-26T06:23:00Z"",""act_deep"":63,""act_light"":280,""act_rem"":32,""act_wake"":94,""time_sedentary"":469,""id"":1346}'| '{""dep_count"":7,""dep_duration"":13,""happy_count"":37,""qual"":89,""end_datetime"":""2019-01-27T13:51:00Z"",""date"":""2019-01-27"",""mid"":""2019-01-27T09:00:00Z"",""counter_report"":0,""counter_nap"":516,""counter_dep"":67,""counter_to_eat"":0,""stress_count"":30,""stress_duration"":54,""start_datetime"":""2019-01-27T04:08:00Z"",""act_deep"":102,""act_light"":302,""act_rem"":89,""act_wake"":90,""time_sedentary"":583,""id"":1346}'| '{""dep_count"":2,""dep_duration"":7,""happy_count"":19,""qual"":88,""end_datetime"":""2019-01-30T10:27:30Z"",""date"":""2019-01-30"",""mid"":""2019-01-30T07:09:30Z"",""counter_report"":9,""counter_nap"":342,""counter_dep"":45,""counter_to_eat"":0,""stress_count"":17,""stress_duration"":47,""start_datetime"":""2019-01-30T03:51:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":396,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":0,""qual"":94,""end_datetime"":""2019-01-31T18:36:30Z"",""date"":""2019-01-31"",""mid"":""2019-01-31T15:37:30Z"",""counter_report"":11,""counter_nap"":325,""counter_dep"":22,""counter_to_eat"":0,""stress_count"":0,""stress_duration"":0,""start_datetime"":""2019-01-31T12:38:30Z"",""act_deep"":53,""act_light"":192,""act_rem"":60,""act_wake"":53,""time_sedentary"":358,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":17,""qual"":91,""end_datetime"":""2019-02-01T12:38:30Z"",""date"":""2019-02-01"",""mid"":""2019-02-01T09:41:00Z"",""counter_report"":0,""counter_nap"":323,""counter_dep"":31,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":28,""start_datetime"":""2019-02-01T06:44:00Z"",""act_deep"":62,""act_light"":170,""act_rem"":58,""act_wake"":64,""time_sedentary"":354,""id"":1346}'| '{""dep_count"":1,""dep_duration"":3,""happy_count"":11,""qual"":91,""end_datetime"":""2019-02-02T12:29:00Z"",""date"":""2019-02-02"",""mid"":""2019-02-02T09:57:30Z"",""counter_report"":0,""counter_nap"":277,""counter_dep"":26,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":23,""start_datetime"":""2019-02-02T07:25:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":303,""id"":1346}'| '{""dep_count"":2,""dep_duration"":6,""happy_count"":28,""qual"":90,""end_datetime"":""2019-02-03T11:16:00Z"",""date"":""2019-02-03"",""mid"":""2019-02-03T06:55:30Z"",""counter_report"":3,""counter_nap"":466,""counter_dep"":52,""counter_to_eat"":0,""stress_count"":26,""stress_duration"":49,""start_datetime"":""2019-02-03T02:34:30Z"",""act_deep"":88,""act_light"":319,""act_rem"":33,""act_wake"":81,""time_sedentary"":521,""id"":1346}']",1/20/2019,"['{""dep_count"":2,""dep_duration"":2,""happy_count"":12,""qual"":90,""end_datetime"":""2019-01-15T10:15:00Z"",""date"":""2019-01-15"",""mid"":""2019-01-15T07:35:00Z"",""counter_report"":0,""counter_nap"":289,""counter_dep"":32,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":30,""start_datetime"":""2019-01-15T04:54:00Z"",""act_deep"":20,""act_light"":231,""act_rem"":26,""act_wake"":44,""time_sedentary"":321,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":10,""qual"":97,""end_datetime"":""2019-01-16T11:27:30Z"",""date"":""2019-01-16"",""mid"":""2019-01-16T07:42:30Z"",""counter_report"":3,""counter_nap"":433,""counter_dep"":14,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":16,""start_datetime"":""2019-01-16T03:57:30Z"",""act_deep"":100,""act_light"":233,""act_rem"":77,""act_wake"":40,""time_sedentary"":450,""id"":1346}'| '{""dep_count"":4,""dep_duration"":9,""happy_count"":15,""qual"":89,""end_datetime"":""2019-01-18T13:35:30Z"",""date"":""2019-01-18"",""mid"":""2019-01-18T10:32:00Z"",""counter_report"":0,""counter_nap"":325,""counter_dep"":42,""counter_to_eat"":0,""stress_count"":11,""stress_duration"":33,""start_datetime"":""2019-01-18T07:28:00Z"",""act_deep"":28,""act_light"":261,""act_rem"":18,""act_wake"":60,""time_sedentary"":367,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":15,""qual"":96,""end_datetime"":""2019-01-19T13:42:30Z"",""date"":""2019-01-19"",""mid"":""2019-01-19T09:57:00Z"",""counter_report"":0,""counter_nap"":432,""counter_dep"":18,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":16,""start_datetime"":""2019-01-19T06:12:00Z"",""act_deep"":87,""act_light"":229,""act_rem"":38,""act_wake"":96,""time_sedentary"":450,""id"":1346}'| '{""dep_count"":7,""dep_duration"":9,""happy_count"":20,""qual"":92,""end_datetime"":""2019-01-20T17:58:00Z"",""date"":""2019-01-20"",""mid"":""2019-01-20T13:21:00Z"",""counter_report"":0,""counter_nap"":512,""counter_dep"":43,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":34,""start_datetime"":""2019-01-20T08:43:00Z"",""act_deep"":95,""act_light"":290,""act_rem"":83,""act_wake"":87,""time_sedentary"":555,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":23,""qual"":93,""end_datetime"":""2019-01-21T16:33:30Z"",""date"":""2019-01-21"",""mid"":""2019-01-21T11:11:00Z"",""counter_report"":1,""counter_nap"":597,""counter_dep"":46,""counter_to_eat"":0,""stress_count"":23,""stress_duration"":47,""start_datetime"":""2019-01-21T05:49:00Z"",""act_deep"":110,""act_light"":373,""act_rem"":64,""act_wake"":97,""time_sedentary"":644,""id"":1346}'| '{""dep_count"":2,""dep_duration"":5,""happy_count"":18,""qual"":94,""end_datetime"":""2019-01-22T13:24:30Z"",""date"":""2019-01-22"",""mid"":""2019-01-22T09:09:00Z"",""counter_report"":0,""counter_nap"":480,""counter_dep"":30,""counter_to_eat"":0,""stress_count"":16,""stress_duration"":25,""start_datetime"":""2019-01-22T04:54:00Z"",""act_deep"":90,""act_light"":302,""act_rem"":69,""act_wake"":49,""time_sedentary"":510,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":9,""qual"":96,""end_datetime"":""2019-01-23T13:13:00Z"",""date"":""2019-01-23"",""mid"":""2019-01-23T10:27:30Z"",""counter_report"":0,""counter_nap"":317,""counter_dep"":13,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":13,""start_datetime"":""2019-01-23T07:42:30Z"",""act_deep"":58,""act_light"":196,""act_rem"":21,""act_wake"":55,""time_sedentary"":330,""id"":1346}'| '{""dep_count"":1,""dep_duration"":2,""happy_count"":20,""qual"":92,""end_datetime"":""2019-01-24T07:02:00Z"",""date"":""2019-01-24"",""mid"":""2019-01-24T03:01:30Z"",""counter_report"":2,""counter_nap"":443,""counter_dep"":36,""counter_to_eat"":0,""stress_count"":19,""stress_duration"":36,""start_datetime"":""2019-01-23T23:00:30Z"",""act_deep"":84,""act_light"":233,""act_rem"":97,""act_wake"":67,""time_sedentary"":481,""id"":1346}'| '{""dep_count"":4,""dep_duration"":4,""happy_count"":19,""qual"":88,""end_datetime"":""2019-01-25T11:48:00Z"",""date"":""2019-01-25"",""mid"":""2019-01-25T08:43:00Z"",""counter_report"":0,""counter_nap"":326,""counter_dep"":44,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":40,""start_datetime"":""2019-01-25T05:38:00Z"",""act_deep"":55,""act_light"":212,""act_rem"":6,""act_wake"":97,""time_sedentary"":370,""id"":1346}'| '{""dep_count"":3,""dep_duration"":3,""happy_count"":17,""qual"":92,""end_datetime"":""2019-01-26T14:12:30Z"",""date"":""2019-01-26"",""mid"":""2019-01-26T10:18:00Z"",""counter_report"":0,""counter_nap"":430,""counter_dep"":39,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":36,""start_datetime"":""2019-01-26T06:23:00Z"",""act_deep"":63,""act_light"":280,""act_rem"":32,""act_wake"":94,""time_sedentary"":469,""id"":1346}'| '{""dep_count"":7,""dep_duration"":13,""happy_count"":37,""qual"":89,""end_datetime"":""2019-01-27T13:51:00Z"",""date"":""2019-01-27"",""mid"":""2019-01-27T09:00:00Z"",""counter_report"":0,""counter_nap"":516,""counter_dep"":67,""counter_to_eat"":0,""stress_count"":30,""stress_duration"":54,""start_datetime"":""2019-01-27T04:08:00Z"",""act_deep"":102,""act_light"":302,""act_rem"":89,""act_wake"":90,""time_sedentary"":583,""id"":1346}'| '{""dep_count"":2,""dep_duration"":7,""happy_count"":19,""qual"":88,""end_datetime"":""2019-01-30T10:27:30Z"",""date"":""2019-01-30"",""mid"":""2019-01-30T07:09:30Z"",""counter_report"":9,""counter_nap"":342,""counter_dep"":45,""counter_to_eat"":0,""stress_count"":17,""stress_duration"":47,""start_datetime"":""2019-01-30T03:51:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":396,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":0,""qual"":94,""end_datetime"":""2019-01-31T18:36:30Z"",""date"":""2019-01-31"",""mid"":""2019-01-31T15:37:30Z"",""counter_report"":11,""counter_nap"":325,""counter_dep"":22,""counter_to_eat"":0,""stress_count"":0,""stress_duration"":0,""start_datetime"":""2019-01-31T12:38:30Z"",""act_deep"":53,""act_light"":192,""act_rem"":60,""act_wake"":53,""time_sedentary"":358,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":17,""qual"":91,""end_datetime"":""2019-02-01T12:38:30Z"",""date"":""2019-02-01"",""mid"":""2019-02-01T09:41:00Z"",""counter_report"":0,""counter_nap"":323,""counter_dep"":31,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":28,""start_datetime"":""2019-02-01T06:44:00Z"",""act_deep"":62,""act_light"":170,""act_rem"":58,""act_wake"":64,""time_sedentary"":354,""id"":1346}'| '{""dep_count"":1,""dep_duration"":3,""happy_count"":11,""qual"":91,""end_datetime"":""2019-02-02T12:29:00Z"",""date"":""2019-02-02"",""mid"":""2019-02-02T09:57:30Z"",""counter_report"":0,""counter_nap"":277,""counter_dep"":26,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":23,""start_datetime"":""2019-02-02T07:25:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":303,""id"":1346}'| '{""dep_count"":2,""dep_duration"":6,""happy_count"":28,""qual"":90,""end_datetime"":""2019-02-03T11:16:00Z"",""date"":""2019-02-03"",""mid"":""2019-02-03T06:55:30Z"",""counter_report"":3,""counter_nap"":466,""counter_dep"":52,""counter_to_eat"":0,""stress_count"":26,""stress_duration"":49,""start_datetime"":""2019-02-03T02:34:30Z"",""act_deep"":88,""act_light"":319,""act_rem"":33,""act_wake"":81,""time_sedentary"":521,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":15,""qual"":96,""end_datetime"":""2019-02-04T12:25:30Z"",""date"":""2019-02-04"",""mid"":""2019-02-04T09:01:30Z"",""counter_report"":0,""counter_nap"":392,""counter_dep"":17,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":17,""start_datetime"":""2019-02-04T05:36:30Z"",""act_deep"":77,""act_light"":191,""act_rem"":86,""act_wake"":55,""time_sedentary"":409,""id"":1346}']",2/19/2019,"['{""dep_count"":0,""dep_duration"":0,""happy_count"":15,""qual"":96,""end_datetime"":""2019-02-04T12:25:30Z"",""date"":""2019-02-04"",""mid"":""2019-02-04T09:01:30Z"",""counter_report"":0,""counter_nap"":392,""counter_dep"":17,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":17,""start_datetime"":""2019-02-04T05:36:30Z"",""act_deep"":77,""act_light"":191,""act_rem"":86,""act_wake"":55,""time_sedentary"":409,""id"":1346}'| '{""dep_count"":4,""dep_duration"":7,""happy_count"":13,""qual"":93,""end_datetime"":""2019-02-05T09:09:30Z"",""date"":""2019-02-05"",""mid"":""2019-02-05T06:13:00Z"",""counter_report"":0,""counter_nap"":327,""counter_dep"":26,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":19,""start_datetime"":""2019-02-05T03:16:00Z"",""act_deep"":53,""act_light"":227,""act_rem"":30,""act_wake"":43,""time_sedentary"":353,""id"":1346}'| '{""dep_count"":7,""dep_duration"":8,""happy_count"":26,""qual"":74,""end_datetime"":""2019-02-06T15:09:00Z"",""date"":""2019-02-06"",""mid"":""2019-02-06T13:01:00Z"",""counter_report"":0,""counter_nap"":189,""counter_dep"":67,""counter_to_eat"":0,""stress_count"":19,""stress_duration"":59,""start_datetime"":""2019-02-06T10:53:00Z"",""act_deep"":44,""act_light"":152,""act_rem"":6,""act_wake"":54,""time_sedentary"":256,""id"":1346}'| '{""dep_count"":5,""dep_duration"":23,""happy_count"":60,""qual"":88,""end_datetime"":""2019-02-07T15:33:30Z"",""date"":""2019-02-07"",""mid"":""2019-02-07T05:23:30Z"",""counter_report"":1,""counter_nap"":1071,""counter_dep"":148,""counter_to_eat"":0,""stress_count"":55,""stress_duration"":126,""start_datetime"":""2019-02-06T19:13:30Z"",""act_deep"":201,""act_light"":783,""act_rem"":65,""act_wake"":171,""time_sedentary"":1220,""id"":1346}'| '{""dep_count"":13,""dep_duration"":18,""happy_count"":47,""qual"":83,""end_datetime"":""2019-02-08T15:23:30Z"",""date"":""2019-02-08"",""mid"":""2019-02-08T10:09:30Z"",""counter_report"":0,""counter_nap"":519,""counter_dep"":109,""counter_to_eat"":0,""stress_count"":34,""stress_duration"":91,""start_datetime"":""2019-02-08T04:55:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":628,""id"":1346}'| '{""dep_count"":6,""dep_duration"":14,""happy_count"":47,""qual"":87,""end_datetime"":""2019-02-09T13:55:30Z"",""date"":""2019-02-09"",""mid"":""2019-02-09T07:47:00Z"",""counter_report"":0,""counter_nap"":641,""counter_dep"":96,""counter_to_eat"":0,""stress_count"":41,""stress_duration"":82,""start_datetime"":""2019-02-09T01:38:00Z"",""act_deep"":90,""act_light"":426,""act_rem"":105,""act_wake"":116,""time_sedentary"":737,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":6,""qual"":86,""end_datetime"":""2019-02-10T08:04:30Z"",""date"":""2019-02-10"",""mid"":""2019-02-10T07:19:00Z"",""counter_report"":0,""counter_nap"":78,""counter_dep"":13,""counter_to_eat"":0,""stress_count"":6,""stress_duration"":13,""start_datetime"":""2019-02-10T06:33:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":91,""id"":1346}'| '{""dep_count"":4,""dep_duration"":8,""happy_count"":24,""qual"":89,""end_datetime"":""2019-02-11T10:42:00Z"",""date"":""2019-02-11"",""mid"":""2019-02-11T07:05:00Z"",""counter_report"":0,""counter_nap"":388,""counter_dep"":47,""counter_to_eat"":0,""stress_count"":20,""stress_duration"":39,""start_datetime"":""2019-02-11T03:27:00Z"",""act_deep"":49,""act_light"":279,""act_rem"":56,""act_wake"":51,""time_sedentary"":435,""id"":1346}'| '{""dep_count"":3,""dep_duration"":3,""happy_count"":7,""qual"":93,""end_datetime"":""2019-02-12T10:29:00Z"",""date"":""2019-02-12"",""mid"":""2019-02-12T09:12:00Z"",""counter_report"":0,""counter_nap"":144,""counter_dep"":11,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":8,""start_datetime"":""2019-02-12T07:54:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":155,""id"":1346}'| '{""dep_count"":1,""dep_duration"":3,""happy_count"":9,""qual"":95,""end_datetime"":""2019-02-14T15:28:00Z"",""date"":""2019-02-14"",""mid"":""2019-02-14T12:19:00Z"",""counter_report"":0,""counter_nap"":361,""counter_dep"":18,""counter_to_eat"":0,""stress_count"":8,""stress_duration"":15,""start_datetime"":""2019-02-14T09:09:00Z"",""act_deep"":81,""act_light"":206,""act_rem"":40,""act_wake"":52,""time_sedentary"":379,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":25,""qual"":95,""end_datetime"":""2019-02-15T11:30:00Z"",""date"":""2019-02-15"",""mid"":""2019-02-15T05:37:00Z"",""counter_report"":0,""counter_nap"":670,""counter_dep"":36,""counter_to_eat"":0,""stress_count"":24,""stress_duration"":35,""start_datetime"":""2019-02-14T23:44:00Z"",""act_deep"":126,""act_light"":395,""act_rem"":85,""act_wake"":100,""time_sedentary"":706,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":14,""qual"":94,""end_datetime"":""2019-02-16T13:30:00Z"",""date"":""2019-02-16"",""mid"":""2019-02-16T10:23:00Z"",""counter_report"":0,""counter_nap"":353,""counter_dep"":22,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":22,""start_datetime"":""2019-02-16T07:15:00Z"",""act_deep"":45,""act_light"":211,""act_rem"":51,""act_wake"":68,""time_sedentary"":375,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":18,""qual"":95,""end_datetime"":""2019-02-17T12:05:30Z"",""date"":""2019-02-17"",""mid"":""2019-02-17T08:11:00Z"",""counter_report"":0,""counter_nap"":444,""counter_dep"":25,""counter_to_eat"":0,""stress_count"":18,""stress_duration"":25,""start_datetime"":""2019-02-17T04:16:00Z"",""act_deep"":77,""act_light"":261,""act_rem"":62,""act_wake"":69,""time_sedentary"":469,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":6,""qual"":89,""end_datetime"":""2019-02-18T13:01:30Z"",""date"":""2019-02-18"",""mid"":""2019-02-18T06:34:30Z"",""counter_report"":0,""counter_nap"":686,""counter_dep"":88,""counter_to_eat"":0,""stress_count"":5,""stress_duration"":10,""start_datetime"":""2019-02-18T00:07:30Z"",""act_deep"":107,""act_light"":475,""act_rem"":75,""act_wake"":117,""time_sedentary"":774,""id"":1346}'| '{""dep_count"":3,""dep_duration"":9,""happy_count"":26,""qual"":90,""end_datetime"":""2019-02-19T13:10:30Z"",""date"":""2019-02-19"",""mid"":""2019-02-19T08:56:30Z"",""counter_report"":0,""counter_nap"":457,""counter_dep"":52,""counter_to_eat"":0,""stress_count"":23,""stress_duration"":43,""start_datetime"":""2019-02-19T04:41:30Z"",""act_deep"":67,""act_light"":276,""act_rem"":91,""act_wake"":75,""time_sedentary"":509,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":25,""qual"":95,""end_datetime"":""2019-02-21T13:26:30Z"",""date"":""2019-02-21"",""mid"":""2019-02-21T08:07:30Z"",""counter_report"":0,""counter_nap"":607,""counter_dep"":31,""counter_to_eat"":0,""stress_count"":24,""stress_duration"":30,""start_datetime"":""2019-02-21T02:48:30Z"",""act_deep"":125,""act_light"":334,""act_rem"":108,""act_wake"":71,""time_sedentary"":638,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":6,""qual"":89,""end_datetime"":""2019-02-22T13:41:00Z"",""date"":""2019-02-22"",""mid"":""2019-02-22T12:54:30Z"",""counter_report"":0,""counter_nap"":82,""counter_dep"":10,""counter_to_eat"":0,""stress_count"":6,""stress_duration"":10,""start_datetime"":""2019-02-22T12:08:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":92,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":4,""qual"":91,""end_datetime"":""2019-02-23T04:58:00Z"",""date"":""2019-02-23"",""mid"":""2019-02-23T04:00:30Z"",""counter_report"":0,""counter_nap"":105,""counter_dep"":10,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":10,""start_datetime"":""2019-02-23T03:02:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":115,""id"":1346}'| '{""dep_count"":1,""dep_duration"":5,""happy_count"":15,""qual"":91,""end_datetime"":""2019-02-25T10:17:00Z"",""date"":""2019-02-25"",""mid"":""2019-02-25T03:44:00Z"",""counter_report"":6,""counter_nap"":712,""counter_dep"":68,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":21,""start_datetime"":""2019-02-24T21:11:00Z"",""act_deep"":112,""act_light"":633,""act_rem"":103,""act_wake"":135,""time_sedentary"":786,""id"":1346}'| '{""dep_count"":2,""dep_duration"":5,""happy_count"":26,""qual"":90,""end_datetime"":""2019-02-26T15:14:00Z"",""date"":""2019-02-26"",""mid"":""2019-02-26T07:48:30Z"",""counter_report"":0,""counter_nap"":803,""counter_dep"":87,""counter_to_eat"":0,""stress_count"":24,""stress_duration"":47,""start_datetime"":""2019-02-26T00:23:30Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":890,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":11,""qual"":82,""end_datetime"":""2019-02-27T10:55:00Z"",""date"":""2019-02-27"",""mid"":""2019-02-27T09:30:30Z"",""counter_report"":5,""counter_nap"":135,""counter_dep"":29,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":33,""start_datetime"":""2019-02-27T08:05:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":169,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":12,""qual"":91,""end_datetime"":""2019-02-28T05:17:30Z"",""date"":""2019-02-28"",""mid"":""2019-02-28T02:49:00Z"",""counter_report"":0,""counter_nap"":269,""counter_dep"":28,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":26,""start_datetime"":""2019-02-28T00:20:00Z"",""act_deep"":19,""act_light"":224,""act_rem"":5,""act_wake"":49,""time_sedentary"":297,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":13,""qual"":97,""end_datetime"":""2019-03-02T13:20:30Z"",""date"":""2019-03-02"",""mid"":""2019-03-02T08:49:00Z"",""counter_report"":0,""counter_nap"":528,""counter_dep"":14,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":14,""start_datetime"":""2019-03-02T04:18:00Z"",""act_deep"":93,""act_light"":309,""act_rem"":89,""act_wake"":51,""time_sedentary"":542,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":4,""qual"":90,""end_datetime"":""2019-03-03T17:11:00Z"",""date"":""2019-03-03"",""mid"":""2019-03-03T16:36:30Z"",""counter_report"":0,""counter_nap"":61,""counter_dep"":7,""counter_to_eat"":0,""stress_count"":2,""stress_duration"":5,""start_datetime"":""2019-03-03T16:02:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":68,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":33,""qual"":93,""end_datetime"":""2019-03-04T17:31:00Z"",""date"":""2019-03-04"",""mid"":""2019-03-04T09:37:30Z"",""counter_report"":5,""counter_nap"":877,""counter_dep"":64,""counter_to_eat"":0,""stress_count"":31,""stress_duration"":66,""start_datetime"":""2019-03-04T01:44:30Z"",""act_deep"":177,""act_light"":571,""act_rem"":68,""act_wake"":130,""time_sedentary"":946,""id"":1346}'| '{""dep_count"":1,""dep_duration"":3,""happy_count"":12,""qual"":95,""end_datetime"":""2019-03-06T12:05:30Z"",""date"":""2019-03-06"",""mid"":""2019-03-06T08:12:00Z"",""counter_report"":1,""counter_nap"":444,""counter_dep"":21,""counter_to_eat"":0,""stress_count"":11,""stress_duration"":19,""start_datetime"":""2019-03-06T04:19:00Z"",""act_deep"":95,""act_light"":257,""act_rem"":64,""act_wake"":50,""time_sedentary"":466,""id"":1346}']",3/22/2019,"['{""dep_count"":4,""dep_duration"":9,""happy_count"":25,""qual"":90,""end_datetime"":""2019-03-07T07:36:00Z"",""date"":""2019-03-07"",""mid"":""2019-03-07T04:10:00Z"",""counter_report"":0,""counter_nap"":370,""counter_dep"":42,""counter_to_eat"":0,""stress_count"":21,""stress_duration"":33,""start_datetime"":""2019-03-07T00:44:00Z"",""act_deep"":61,""act_light"":247,""act_rem"":62,""act_wake"":42,""time_sedentary"":412,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":4,""qual"":88,""end_datetime"":""2019-03-08T15:44:30Z"",""date"":""2019-03-08"",""mid"":""2019-03-08T14:47:00Z"",""counter_report"":0,""counter_nap"":100,""counter_dep"":14,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":14,""start_datetime"":""2019-03-08T13:50:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":114,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":16,""qual"":94,""end_datetime"":""2019-03-10T05:37:00Z"",""date"":""2019-03-10"",""mid"":""2019-03-10T01:17:00Z"",""counter_report"":0,""counter_nap"":488,""counter_dep"":33,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":31,""start_datetime"":""2019-03-09T20:56:00Z"",""act_deep"":108,""act_light"":415,""act_rem"":113,""act_wake"":86,""time_sedentary"":521,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":15,""qual"":92,""end_datetime"":""2019-03-11T14:43:00Z"",""date"":""2019-03-11"",""mid"":""2019-03-11T12:12:00Z"",""counter_report"":0,""counter_nap"":278,""counter_dep"":25,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":23,""start_datetime"":""2019-03-11T09:40:00Z"",""act_deep"":56,""act_light"":186,""act_rem"":22,""act_wake"":39,""time_sedentary"":303,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":3,""qual"":99,""end_datetime"":""2019-03-12T12:01:00Z"",""date"":""2019-03-12"",""mid"":""2019-03-12T08:55:00Z"",""counter_report"":0,""counter_nap"":370,""counter_dep"":3,""counter_to_eat"":0,""stress_count"":3,""stress_duration"":3,""start_datetime"":""2019-03-12T05:48:00Z"",""act_deep"":93,""act_light"":226,""act_rem"":15,""act_wake"":39,""time_sedentary"":373,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":22,""qual"":93,""end_datetime"":""2019-03-13T13:51:00Z"",""date"":""2019-03-13"",""mid"":""2019-03-13T08:54:30Z"",""counter_report"":0,""counter_nap"":553,""counter_dep"":40,""counter_to_eat"":0,""stress_count"":21,""stress_duration"":39,""start_datetime"":""2019-03-13T03:57:30Z"",""act_deep"":91,""act_light"":375,""act_rem"":47,""act_wake"":80,""time_sedentary"":593,""id"":1346}'| '{""dep_count"":4,""dep_duration"":6,""happy_count"":32,""qual"":89,""end_datetime"":""2019-03-14T14:19:00Z"",""date"":""2019-03-14"",""mid"":""2019-03-14T09:28:00Z"",""counter_report"":0,""counter_nap"":519,""counter_dep"":64,""counter_to_eat"":0,""stress_count"":28,""stress_duration"":58,""start_datetime"":""2019-03-14T04:36:00Z"",""act_deep"":113,""act_light"":314,""act_rem"":46,""act_wake"":110,""time_sedentary"":583,""id"":1346}'| '{""dep_count"":1,""dep_duration"":3,""happy_count"":5,""qual"":91,""end_datetime"":""2019-03-15T06:06:00Z"",""date"":""2019-03-15"",""mid"":""2019-03-15T05:25:00Z"",""counter_report"":9,""counter_nap"":67,""counter_dep"":7,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":13,""start_datetime"":""2019-03-15T04:43:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":83,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":14,""qual"":95,""end_datetime"":""2019-03-16T13:19:30Z"",""date"":""2019-03-16"",""mid"":""2019-03-16T09:17:00Z"",""counter_report"":0,""counter_nap"":461,""counter_dep"":23,""counter_to_eat"":0,""stress_count"":14,""stress_duration"":23,""start_datetime"":""2019-03-16T05:15:00Z"",""act_deep"":84,""act_light"":307,""act_rem"":54,""act_wake"":39,""time_sedentary"":484,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":3,""qual"":99,""end_datetime"":""2019-03-18T12:05:30Z"",""date"":""2019-03-18"",""mid"":""2019-03-18T08:23:30Z"",""counter_report"":6,""counter_nap"":432,""counter_dep"":6,""counter_to_eat"":0,""stress_count"":3,""stress_duration"":4,""start_datetime"":""2019-03-18T04:41:30Z"",""act_deep"":0,""act_light"":216,""act_rem"":12,""act_wake"":64,""time_sedentary"":444,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":12,""qual"":93,""end_datetime"":""2019-03-19T12:06:30Z"",""date"":""2019-03-19"",""mid"":""2019-03-19T10:13:30Z"",""counter_report"":1,""counter_nap"":209,""counter_dep"":16,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":15,""start_datetime"":""2019-03-19T08:20:30Z"",""act_deep"":31,""act_light"":121,""act_rem"":30,""act_wake"":44,""time_sedentary"":226,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":6,""qual"":84,""end_datetime"":""2019-03-20T09:21:00Z"",""date"":""2019-03-20"",""mid"":""2019-03-20T08:23:30Z"",""counter_report"":0,""counter_nap"":97,""counter_dep"":18,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":15,""start_datetime"":""2019-03-20T07:25:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":115,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":8,""qual"":93,""end_datetime"":""2019-03-21T09:05:00Z"",""date"":""2019-03-21"",""mid"":""2019-03-21T07:45:00Z"",""counter_report"":0,""counter_nap"":148,""counter_dep"":12,""counter_to_eat"":0,""stress_count"":8,""stress_duration"":12,""start_datetime"":""2019-03-21T06:25:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":160,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":3,""qual"":85,""end_datetime"":""2019-03-22T15:12:00Z"",""date"":""2019-03-22"",""mid"":""2019-03-22T13:00:00Z"",""counter_report"":0,""counter_nap"":226,""counter_dep"":39,""counter_to_eat"":0,""stress_count"":2,""stress

duration"":6,""start_datetime"":""2019-03-22T10:47:00Z"",""act_deep"":37,""act_light"":173,""act_rem"":21,""act_wake"":34,""time_sedentary"":265,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":14,""qual"":94,""end_datetime"":""2019-03-23T08:58:00Z"",""date"":""2019-03-23"",""mid"":""2019-03-23T06:14:30Z"",""counter_report"":1,""counter_nap"":305,""counter_dep"":20,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":20,""start_datetime"":""2019-03-23T03:31:30Z"",""act_deep"":25,""act_light"":221,""act_rem"":42,""act_wake"":38,""time_sedentary"":326,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":9,""qual"":90,""end_datetime"":""2019-03-24T20:48:30Z"",""date"":""2019-03-24"",""mid"":""2019-03-24T19:12:30Z"",""counter_report"":0,""counter_nap"":172,""counter_dep"":20,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":20,""start_datetime"":""2019-03-24T17:36:30Z"",""act_deep"":31,""act_light"":127,""act_rem"":11,""act_wake"":23,""time_sedentary"":192,""id"":1346}'| '{""dep_count"":1,""dep_duration"":2,""happy_count"":4,""qual"":97,""end_datetime"":""2019-03-25T09:29:00Z"",""date"":""2019-03-25"",""mid"":""2019-03-25T06:42:00Z"",""counter_report"":0,""counter_nap"":325,""counter_dep"":10,""counter_to_eat"":0,""stress_count"":3,""stress_duration"":8,""start_datetime"":""2019-03-25T03:54:00Z"",""act_deep"":34,""act_light"":196,""act_rem"":82,""act_wake"":23,""time_sedentary"":335,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":6,""qual"":94,""end_datetime"":""2019-03-26T14:11:00Z"",""date"":""2019-03-26"",""mid"":""2019-03-26T11:47:30Z"",""counter_report"":1,""counter_nap"":270,""counter_dep"":16,""counter_to_eat"":0,""stress_count"":6,""stress_duration"":13,""start_datetime"":""2019-03-26T09:23:30Z"",""act_deep"":58,""act_light"":173,""act_rem"":12,""act_wake"":44,""time_sedentary"":287,""id"":1346}'| '{""dep_count"":1,""dep_duration"":2,""happy_count"":11,""qual"":96,""end_datetime"":""2019-03-27T15:36:30Z"",""date"":""2019-03-27"",""mid"":""2019-03-27T11:39:00Z"",""counter_report"":0,""counter_nap"":456,""counter_dep"":19,""counter_to_eat"":0,""stress_count"":10,""stress_duration"":17,""start_datetime"":""2019-03-27T07:41:00Z"",""act_deep"":115,""act_light"":218,""act_rem"":90,""act_wake"":52,""time_sedentary"":475,""id"":1346}'| '{""dep_count"":3,""dep_duration"":4,""happy_count"":11,""qual"":89,""end_datetime"":""2019-03-29T12:44:00Z"",""date"":""2019-03-29"",""mid"":""2019-03-29T07:32:00Z"",""counter_report"":0,""counter_nap"":555,""counter_dep"":70,""counter_to_eat"":0,""stress_count"":8,""stress_duration"":20,""start_datetime"":""2019-03-29T02:19:00Z"",""act_deep"":80,""act_light"":397,""act_rem"":59,""act_wake"":89,""time_sedentary"":625,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":7,""qual"":96,""end_datetime"":""2019-03-31T10:03:00Z"",""date"":""2019-03-31"",""mid"":""2019-03-31T07:18:00Z"",""counter_report"":0,""counter_nap"":318,""counter_dep"":13,""counter_to_eat"":0,""stress_count"":6,""stress_duration"":12,""start_datetime"":""2019-03-31T04:32:00Z"",""act_deep"":58,""act_light"":189,""act_rem"":48,""act_wake"":36,""time_sedentary"":331,""id"":1346}'| '{""dep_count"":3,""dep_duration"":5,""happy_count"":21,""qual"":91,""end_datetime"":""2019-04-01T10:40:30Z"",""date"":""2019-04-01"",""mid"":""2019-04-01T06:45:00Z"",""counter_report"":0,""counter_nap"":430,""counter_dep"":40,""counter_to_eat"":0,""stress_count"":18,""stress_duration"":35,""start_datetime"":""2019-04-01T02:50:00Z"",""act_deep"":104,""act_light"":249,""act_rem"":29,""act_wake"":88,""time_sedentary"":470,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":18,""qual"":93,""end_datetime"":""2019-04-02T11:06:30Z"",""date"":""2019-04-02"",""mid"":""2019-04-02T07:26:00Z"",""counter_report"":0,""counter_nap"":410,""counter_dep"":30,""counter_to_eat"":0,""stress_count"":16,""stress_duration"":28,""start_datetime"":""2019-04-02T03:46:00Z"",""act_deep"":109,""act_light"":228,""act_rem"":31,""act_wake"":72,""time_sedentary"":440,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":3,""qual"":93,""end_datetime"":""2019-04-03T10:15:30Z"",""date"":""2019-04-03"",""mid"":""2019-04-03T05:19:00Z"",""counter_report"":0,""counter_nap"":552,""counter_dep"":41,""counter_to_eat"":0,""stress_count"":2,""stress_duration"":2,""start_datetime"":""2019-04-03T00:22:00Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":593,""id"":1346}'| '{""dep_count"":2,""dep_duration"":2,""happy_count"":9,""qual"":88,""end_datetime"":""2019-04-04T12:46:00Z"",""date"":""2019-04-04"",""mid"":""2019-04-04T06:50:00Z"",""counter_report"":0,""counter_nap"":630,""counter_dep"":83,""counter_to_eat"":0,""stress_count"":7,""stress_duration"":15,""start_datetime"":""2019-04-04T00:53:00Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":713,""id"":1346}'| '{""dep_count"":5,""dep_duration"":6,""happy_count"":20,""qual"":91,""end_datetime"":""2019-04-05T10:16:30Z"",""date"":""2019-04-05"",""mid"":""2019-04-05T03:45:00Z"",""counter_report"":0,""counter_nap"":715,""counter_dep"":68,""counter_to_eat"":0,""stress_count"":15,""stress_duration"":41,""start_datetime"":""2019-04-04T21:13:00Z"",""act_deep"":0,""act_light"":0,""act_rem"":0,""act_wake"":0,""time_sedentary"":783,""id"":1346}']",4/26/2019,"['{""dep_count"":1,""dep_duration"":1,""happy_count"":10,""qual"":97,""end_datetime"":""2019-04-11T10:25:00Z"",""date"":""2019-04-11"",""mid"":""2019-04-11T06:56:00Z"",""counter_report"":0,""counter_nap"":405,""counter_dep"":14,""counter_to_eat"":0,""stress_count"":9,""stress_duration"":13,""start_datetime"":""2019-04-11T03:26:00Z"",""act_deep"":97,""act_light"":181,""act_rem"":93,""act_wake"":48,""time_sedentary"":419,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":2,""qual"":97,""end_datetime"":""2019-04-12T09:44:30Z"",""date"":""2019-04-12"",""mid"":""2019-04-12T08:07:00Z"",""counter_report"":0,""counter_nap"":189,""counter_dep"":5,""counter_to_eat"":0,""stress_count"":2,""stress_duration"":5,""start_datetime"":""2019-04-12T06:30:00Z"",""act_deep"":56,""act_light"":90,""act_rem"":33,""act_wake"":15,""time_sedentary"":194,""id"":1346}'| '{""dep_count"":1,""dep_duration"":1,""happy_count"":7,""qual"":96,""end_datetime"":""2019-04-13T16:57:30Z"",""date"":""2019-04-13"",""mid"":""2019-04-13T15:19:30Z"",""counter_report"":7,""counter_nap"":183,""counter_dep"":7,""counter_to_eat"":0,""stress_count"":6,""stress_duration"":13,""start_datetime"":""2019-04-13T13:40:30Z"",""act_deep"":48,""act_light"":105,""act_rem"":23,""act_wake"":21,""time_sedentary"":197,""id"":1346}'| '{""dep_count"":2,""dep_duration"":5,""happy_count"":18,""qual"":93,""end_datetime"":""2019-04-14T10:56:30Z"",""date"":""2019-04-14"",""mid"":""2019-04-14T06:36:30Z"",""counter_report"":0,""counter_nap"":487,""counter_dep"":34,""counter_to_eat"":0,""stress_count"":16,""stress_duration"":29,""start_datetime"":""2019-04-14T02:15:30Z"",""act_deep"":77,""act_light"":332,""act_rem"":50,""act_wake"":62,""time_sedentary"":521,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":22,""qual"":90,""end_datetime"":""2019-04-15T11:06:00Z"",""date"":""2019-04-15"",""mid"":""2019-04-15T07:58:00Z"",""counter_report"":5,""counter_nap"":333,""counter_dep"":38,""counter_to_eat"":0,""stress_count"":20,""stress_duration"":40,""start_datetime"":""2019-04-15T04:50:00Z"",""act_deep"":58,""act_light"":196,""act_rem"":49,""act_wake"":73,""time_sedentary"":376,""id"":1346}']",5/22/2019,"['{""dep_count"":1,""dep_duration"":3,""happy_count"":1,""qual"":99,""end_datetime"":""2019-06-01T09:10:30Z"",""date"":""2019-06-01"",""mid"":""2019-06-01T06:54:00Z"",""counter_report"":0,""counter_nap"":270,""counter_dep"":3,""counter_to_eat"":0,""stress_count"":0,""stress_duration"":0,""start_datetime"":""2019-06-01T04:37:00Z"",""act_deep"":35,""act_light"":170,""act_rem"":42,""act_wake"":26,""time_sedentary"":273,""id"":1346}'| '{""dep_count"":1,""dep_duration"":4,""happy_count"":4,""qual"":85,""end_datetime"":""2019-06-02T05:29:30Z"",""date"":""2019-06-02"",""mid"":""2019-06-02T04:56:30Z"",""counter_report"":0,""counter_nap"":57,""counter_dep"":10,""counter_to_eat"":0,""stress_count"":3,""stress_duration"":6,""start_datetime"":""2019-06-02T04:22:30Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":67,""id"":1346}']",6/20/2019,"['{""dep_count"":0,""dep_duration"":0,""happy_count"":7,""qual"":97,""end_datetime"":""2019-06-11T08:49:00Z"",""date"":""2019-06-11"",""mid"":""2019-06-11T06:28:00Z"",""counter_report"":3,""counter_nap"":270,""counter_dep"":9,""counter_to_eat"":0,""stress_count"":7,""stress_duration"":12,""start_datetime"":""2019-06-11T04:07:00Z"",""act_deep"":50,""act_light"":174,""act_rem"":18,""act_wake"":40,""time_sedentary"":282,""id"":1346}'| '{""dep_count"":1,""dep_duration"":2,""happy_count"":5,""qual"":94,""end_datetime"":""2019-06-12T16:32:30Z"",""date"":""2019-06-12"",""mid"":""2019-06-12T15:08:00Z"",""counter_report"":0,""counter_nap"":158,""counter_dep"":10,""counter_to_eat"":0,""stress_count"":4,""stress_duration"":8,""start_datetime"":""2019-06-12T13:44:00Z"",""act_deep"":null,""act_light"":null,""act_rem"":null,""act_wake"":null,""time_sedentary"":168,""id"":1346}'| '{""dep_count"":2,""dep_duration"":3,""happy_count"":15,""qual"":93,""end_datetime"":""2019-06-13T09:44:00Z"",""date"":""2019-06-13"",""mid"":""2019-06-13T05:47:00Z"",""counter_report"":0,""counter_nap"":442,""counter_dep"":33,""counter_to_eat"":0,""stress_count"":13,""stress_duration"":30,""start_datetime"":""2019-06-13T01:49:00Z"",""act_deep"":80,""act_light"":252,""act_rem"":75,""act_wake"":68,""time_sedentary"":475,""id"":1346}'| '{""dep_count"":4,""dep_duration"":6,""happy_count"":23,""qual"":93,""end_datetime"":""2019-06-15T13:30:30Z"",""date"":""2019-06-15"",""mid"":""2019-06-15T07:15:30Z"",""counter_report"":0,""counter_nap"":698,""counter_dep"":52,""counter_to_eat"":0,""stress_count"":19,""stress_duration"":46,""start_datetime"":""2019-06-15T01:00:30Z"",""act_deep"":166,""act_light"":364,""act_rem"":115,""act_wake"":105,""time_sedentary"":750,""id"":1346}'| '{""dep_count"":3,""dep_duration"":6,""happy_count"":11,""qual"":94,""end_datetime"":""2019-06-16T16:14:00Z"",""date"":""2019-06-16"",""mid"":""2019-06-16T14:31:00Z"",""counter_report"":12,""counter_nap"":183,""counter_dep"":12,""counter_to_eat"":0,""stress_count"":8,""stress_duration"":18,""start_datetime"":""2019-06-16T12:47:00Z"",""act_deep"":52,""act_light"":124,""act_rem"":6,""act_wake"":25,""time_sedentary"":207,""id"":1346}'| '{""dep_count"":0,""dep_duration"":0,""happy_count"":7,""qual"":97,""end_datetime"":""2019-06-17T08:01:00Z"",""date"":""2019-06-17"",""mid"":""2019-06-17T05:53:00Z"",""counter_report"":0,""counter_nap"":249,""counter_dep"":8,""counter_to_eat"":0,""stress_count"":7,""stress_duration"":8,""start_datetime"":""2019-06-17T03:44:00Z"",""act_deep"":56,""act_light"":131,""act_rem"":31,""act_wake"":39,""time_sedentary"":257,""id"":1346}'| '{""dep_count"":3,""dep_duration"":4,""happy_count"":15,""qual"":88,""end_datetime"":""2019-06-26T15:54:30Z"",""date"":""2019-06-26"",""mid"":""2019-06-26T10:43:30Z"",""counter_report"":0,""counter_nap"":547,""counter_dep"":76,""counter_to_eat"":0,""stress_count"":12,""stress_duration"":25,""start_datetime"":""2019-06-26T05:31:30Z"",""act_deep"":99,""act_light"":366,""act_rem"":80,""act_wake"":78,""time_sedentary"":623,""id"":1346}']",7/22/2019,[],8/20/2019,[],9/23/2019,[],10/18/2019,[],1/18/2020,[],2/17/2020,[],3/19/2020,[],,[],,[],,[],,[],,[],,[],,[],,[],,,,,

Then I imported the data into SAS and "have" looks like this (first two rows and 11 columns shown).

Obs	id	anx_1_date	anx_1_PA	dep_1_date	dep_1_PA	dep_2_date	dep_2_PA	dep_3_date	dep_3_PA	dep_4_date	dep_4_PA
1	1346	1/19/2019	['{"dep_count":2,"dep_duration":2,"happy_count":12,"qual":90,"end_datetime":"2019-01-15T10:15:00Z","date":"2019-01-15","mid":"2019-01-15T07:35:00Z","counter_report":0,"counter_nap":289,"counter_dep":32,"counter_to_eat":0,"stress_count":10,"stress_duration":30,"start_datetime":"2019-01-15T04:54:00Z","act_deep":20,"act_light":231,"act_rem":26,"act_wake":44,"time_sedentary":321,"id":1346}'\| '{"dep_count":1,"dep_duration":1,"happy_count":10,"qual":97,"end_datetime":"2019-01-16T11:27:30Z","date":"2019-01-16","mid":"2019-01-16T07:42:30Z","counter_report":3,"counter_nap":433,"counter_dep":14,"counter_to_eat":0,"stress_count":9,"stress_duration":16,"start_datetime":"2019-01-16T03:57:30Z","act_deep":100,"act_light":233,"act_rem":77,"act_wake":40,"time_sedentary":450,"id":1346}'\| '{"dep_count":4,"dep_duration":9,"happy_count":15,"qual":89,"end_datetime":"2019-01-18T13:35:30Z","date":"2019-01-18","mid":"2019-01-18T10:32:00Z","counter_report":0,"counter_nap":325,"counter_dep":42,"counter_to_eat":0,"stress_count":11,"stress_duration":33,"start_datetime":"2019-01-18T07:28:00Z","act_deep":28,"act_light":261,"act_rem":18,"act_wake":60,"time_sedentary":367,"id":1346}'\| '{"dep_count":2,"dep_duration":2,"happy_count":15,"qual":96,"end_datetime":"2019-01-19T13:42:30Z","date":"2019-01-19","mid":"2019-01-19T09:57:00Z","counter_report":0,"counter_nap":432,"counter_dep":18,"counter_to_eat":0,"stress_count":13,"stress_duration":16,"start_datetime":"2019-01-19T06:12:00Z","act_deep":87,"act_light":229,	1/20/2019	['{"dep_count":2,"dep_duration":2,"happy_count":12,"qual":90,"end_datetime":"2019-01-15T10:15:00Z","date":"2019-01-15","mid":"2019-01-15T07:35:00Z","counter_report":0,"counter_nap":289,"counter_dep":32,"counter_to_eat":0,"stress_count":10,"stress_duration":30,"start_datetime":"2019-01-15T04:54:00Z","act_deep":20,"act_light":231,"act_rem":26,"act_wake":44,"time_sedentary":321,"id":1346}'\| '{"dep_count":1,"dep_duration":1,"happy_count":10,"qual":97,"end_datetime":"2019-01-16T11:27:30Z","date":"2019-01-16","mid":"2019-01-16T07:42:30Z","counter_report":3,"counter_nap":433,"counter_dep":14,"counter_to_eat":0,"stress_count":9,"stress_duration":16,"start_datetime":"2019-01-16T03:57:30Z","act_deep":100,"act_light":233,"act_rem":77,"act_wake":40,"time_sedentary":450,"id":1346}'\| '{"dep_count":4,"dep_duration":9,"happy_count":15,"qual":89,"end_datetime":"2019-01-18T13:35:30Z","date":"2019-01-18","mid":"2019-01-18T10:32:00Z","counter_report":0,"counter_nap":325,"counter_dep":42,"counter_to_eat":0,"stress_count":11,"stress_duration":33,"start_datetime":"2019-01-18T07:28:00Z","act_deep":28,"act_light":261,"act_rem":18,"act_wake":60,"time_sedentary":367,"id":1346}'\| '{"dep_count":2,"dep_duration":2,"happy_count":15,"qual":96,"end_datetime":"2019-01-19T13:42:30Z","date":"2019-01-19","mid":"2019-01-19T09:57:00Z","counter_report":0,"counter_nap":432,"counter_dep":18,"counter_to_eat":0,"stress_count":13,"stress_duration":16,"start_datetime":"2019-01-19T06:12:00Z","act_deep":87,"act_light":229,	2/19/2019	['{"dep_count":0,"dep_duration":0,"happy_count":15,"qual":96,"end_datetime":"2019-02-04T12:25:30Z","date":"2019-02-04","mid":"2019-02-04T09:01:30Z","counter_report":0,"counter_nap":392,"counter_dep":17,"counter_to_eat":0,"stress_count":15,"stress_duration":17,"start_datetime":"2019-02-04T05:36:30Z","act_deep":77,"act_light":191,"act_rem":86,"act_wake":55,"time_sedentary":409,"id":1346}'\| '{"dep_count":4,"dep_duration":7,"happy_count":13,"qual":93,"end_datetime":"2019-02-05T09:09:30Z","date":"2019-02-05","mid":"2019-02-05T06:13:00Z","counter_report":0,"counter_nap":327,"counter_dep":26,"counter_to_eat":0,"stress_count":9,"stress_duration":19,"start_datetime":"2019-02-05T03:16:00Z","act_deep":53,"act_light":227,"act_rem":30,"act_wake":43,"time_sedentary":353,"id":1346}'\| '{"dep_count":7,"dep_duration":8,"happy_count":26,"qual":74,"end_datetime":"2019-02-06T15:09:00Z","date":"2019-02-06","mid":"2019-02-06T13:01:00Z","counter_report":0,"counter_nap":189,"counter_dep":67,"counter_to_eat":0,"stress_count":19,"stress_duration":59,"start_datetime":"2019-02-06T10:53:00Z","act_deep":44,"act_light":152,"act_rem":6,"act_wake":54,"time_sedentary":256,"id":1346}'\| '{"dep_count":5,"dep_duration":23,"happy_count":60,"qual":88,"end_datetime":"2019-02-07T15:33:30Z","date":"2019-02-07","mid":"2019-02-07T05:23:30Z","counter_report":1,"counter_nap":1071,"counter_dep":148,"counter_to_eat":0,"stress_count":55,"stress_duration":126,"start_datetime":"2019-02-06T19:13:30Z","act_deep":201,"act_light":7	3/22/2019	"['{""dep_count"":4	.	""happy_count"":25
2	1498	1/28/2019	['{"dep_count":4,"dep_duration":4,"happy_count":10,"qual":95,"end_datetime":"2019-01-25T06:44:30Z","date":"2019-01-25","mid":"2019-01-25T04:08:00Z","counter_report":0,"counter_nap":297,"counter_dep":15,"counter_to_eat":0,"stress_count":6,"stress_duration":11,"start_datetime":"2019-01-25T01:32:00Z","act_deep":52,"act_light":175,"act_rem":55,"act_wake":30,"time_sedentary":312,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":5,"qual":96,"end_datetime":"2019-01-26T07:15:30Z","date":"2019-01-26","mid":"2019-01-26T04:39:30Z","counter_report":0,"counter_nap":300,"counter_dep":13,"counter_to_eat":0,"stress_count":5,"stress_duration":13,"start_datetime":"2019-01-26T02:02:30Z","act_deep":54,"act_light":179,"act_rem":50,"act_wake":30,"time_sedentary":313,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":3,"qual":97,"end_datetime":"2019-01-27T06:30:00Z","date":"2019-01-27","mid":"2019-01-27T04:33:00Z","counter_report":0,"counter_nap":228,"counter_dep":7,"counter_to_eat":0,"stress_count":3,"stress_duration":7,"start_datetime":"2019-01-27T02:35:00Z","act_deep":33,"act_light":162,"act_rem":20,"act_wake":20,"time_sedentary":235,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":3,"qual":98,"end_datetime":"2019-01-28T06:37:30Z","date":"2019-01-28","mid":"2019-01-28T04:25:00Z","counter_report":0,"counter_nap":258,"counter_dep":6,"counter_to_eat":0,"stress_count":3,"stress_duration":6,"start_datetime":"2019-01-28T02:13:00Z","act_deep":0,"act_light":0,"act_rem":0,"a	1/30/2019	['{"dep_count":4,"dep_duration":4,"happy_count":10,"qual":95,"end_datetime":"2019-01-25T06:44:30Z","date":"2019-01-25","mid":"2019-01-25T04:08:00Z","counter_report":0,"counter_nap":297,"counter_dep":15,"counter_to_eat":0,"stress_count":6,"stress_duration":11,"start_datetime":"2019-01-25T01:32:00Z","act_deep":52,"act_light":175,"act_rem":55,"act_wake":30,"time_sedentary":312,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":5,"qual":96,"end_datetime":"2019-01-26T07:15:30Z","date":"2019-01-26","mid":"2019-01-26T04:39:30Z","counter_report":0,"counter_nap":300,"counter_dep":13,"counter_to_eat":0,"stress_count":5,"stress_duration":13,"start_datetime":"2019-01-26T02:02:30Z","act_deep":54,"act_light":179,"act_rem":50,"act_wake":30,"time_sedentary":313,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":3,"qual":97,"end_datetime":"2019-01-27T06:30:00Z","date":"2019-01-27","mid":"2019-01-27T04:33:00Z","counter_report":0,"counter_nap":228,"counter_dep":7,"counter_to_eat":0,"stress_count":3,"stress_duration":7,"start_datetime":"2019-01-27T02:35:00Z","act_deep":33,"act_light":162,"act_rem":20,"act_wake":20,"time_sedentary":235,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":3,"qual":98,"end_datetime":"2019-01-28T06:37:30Z","date":"2019-01-28","mid":"2019-01-28T04:25:00Z","counter_report":0,"counter_nap":258,"counter_dep":6,"counter_to_eat":0,"stress_count":3,"stress_duration":6,"start_datetime":"2019-01-28T02:13:00Z","act_deep":0,"act_light":0,"act_rem":0,"a	2/28/2019	['{"dep_count":0,"dep_duration":0,"happy_count":7,"qual":99,"end_datetime":"2019-02-13T10:41:30Z","date":"2019-02-13","mid":"2019-02-13T06:12:30Z","counter_report":0,"counter_nap":530,"counter_dep":8,"counter_to_eat":0,"stress_count":7,"stress_duration":8,"start_datetime":"2019-02-13T01:43:30Z","act_deep":0,"act_light":0,"act_rem":0,"act_wake":0,"time_sedentary":538,"id":1498}'\| '{"dep_count":1,"dep_duration":1,"happy_count":4,"qual":99,"end_datetime":"2019-02-14T06:46:00Z","date":"2019-02-14","mid":"2019-02-14T04:13:30Z","counter_report":6,"counter_nap":296,"counter_dep":2,"counter_to_eat":0,"stress_count":3,"stress_duration":7,"start_datetime":"2019-02-14T01:41:30Z","act_deep":49,"act_light":198,"act_rem":31,"act_wake":26,"time_sedentary":304,"id":1498}'\| '{"dep_count":0,"dep_duration":0,"happy_count":7,"qual":97,"end_datetime":"2019-02-15T06:36:00Z","date":"2019-02-15","mid":"2019-02-15T04:00:00Z","counter_report":6,"counter_nap":299,"counter_dep":8,"counter_to_eat":0,"stress_count":7,"stress_duration":14,"start_datetime":"2019-02-15T01:23:00Z","act_deep":0,"act_light":0,"act_rem":0,"act_wake":0,"time_sedentary":313,"id":1498}'\| '{"dep_count":1,"dep_duration":1,"happy_count":7,"qual":98,"end_datetime":"2019-02-16T07:40:00Z","date":"2019-02-16","mid":"2019-02-16T04:52:30Z","counter_report":4,"counter_nap":322,"counter_dep":8,"counter_to_eat":0,"stress_count":6,"stress_duration":11,"start_datetime":"2019-02-16T02:05:30Z","act_deep":63,"act_light":179,"act_rem":49,"act_wake":	4/1/2019	"['{""dep_count"":2	.	""happy_count"":12

Obviously, there are some formatting errors here (you can see this clearly in the dep_3_PA, dep_4_date, and dep_4_PA columns). Note that it goes up to dep_21_date/PA.

After running the code from have to tall, below is a preview of "tall" (first 2 rows) - most of the columns are empty:

Obs	id	anx_1_date	dep_1_date	dep_2_date	dep_3_date	dep_4_date	dep_5_date	dep_6_date	dep_7_date	dep_8_date	dep_9_date	dep_10_date	dep_11_date	dep_12_date	dep_13_date	dep_14_date	dep_15_date	dep_16_date	dep_17_date	dep_18_date	dep_19_date	dep_20_date	dep_21_date	col	line	row	name	value	term	record
1	1346	1/19/2019	1/20/2019	2/19/2019	3/22/2019	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1	1	1	dep_count	2	"dep_count":2	['{"dep_count":2,"dep_duration":2,"happy_count":12,"qual":90,"end_datetime":"2019-01-15T10:15:00Z","date":"2019-01-15","mid":"2019-01-15T07:35:00Z","counter_report":0,"counter_nap":289,"counter_dep":32,"counter_to_eat":0,"stress_count":10,"stress_duration":30,"start_datetime":"2019-01-15T04:54:00Z","act_deep":20,"act_light":231,"act_rem":26,"act_wake":44,"time_sedentary":321,"id":1346}'
2	1346	1/19/2019	1/20/2019	2/19/2019	3/22/2019	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1	1	2	dep_duration	2	"dep_duration":2	['{"dep_count":2,"dep_duration":2,"happy_count":12,"qual":90,"end_datetime":"2019-01-15T10:15:00Z","date":"2019-01-15","mid":"2019-01-15T07:35:00Z","counter_report":0,"counter_nap":289,"counter_dep":32,"counter_to_eat":0,"stress_count":10,"stress_duration":30,"start_datetime":"2019-01-15T04:54:00Z","act_deep":20,"act_light":231,"act_rem":26,"act_wake":44,"time_sedentary":321,"id":1346}'

Could you please assist (again)? I've worked on this all day and can't figure out why the sample worked and not my larger dataset.

Many thanks in advance for your help, and apologies again for not providing a proper sample!

Re: Parse and reformat JSON data

Tom — Wed, 16 Aug 2023 19:20:45 GMT

Your file has lines that are over 200,000 bytes long. So make sure to use a large enough LRECL when you read the CSV file into a dataset.

 69         data _null_;
 70           infile fixed lrecl=1000000;
 71           input;
 72         run;
 
 NOTE: The infile FIXED is:
       Filename=/saswork/SAS_workCEC800005E34_odaws01-usw2.oda.sas.com/#LN00019,
       Owner Name=tom.abernathy,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=16Aug2023:12:58:11,
       File Size (bytes)=102385714
 
 NOTE: 1047 records were read from the infile FIXED.
       The minimum record length was 92.
       The maximum record length was 288873.
 NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       cpu time            0.03 seconds

You appear to now have one variable for ANX and 21 variables for DEP. So just read in the CSV file that way to begin with and eliminate one level of looping you will need later.

The embedded line breaks in this version of the file are bare LINEFEED characters and the actual end of lines are CR + LF pairs.

So this data step seems to read in the ORIGINAL CSV file into something that is more reasonable.

data json ;
  length id 8 date 8 type $3 rep 8 json $32000;
  informat date mmddyy.;
  format date yymmdd10.;
  infile '~/json_example.zip' zip member='dep_PA for forum 2.csv'
    dsd truncover fistosb=2 termstr=crlf lrecl=1000000
  ;
  input id @ ;
  type = 'anx'; rep=1;
  input date json @;
  if json='[]' then json=' ';
  else json=translate(json,'|','0A'x);
  output;
  type= 'dep';
  do rep=1 by 1 until(date=. and json=' ');
    input date ?? json @ ;
    if json='[]' then json=' ';
    else json=translate(json,'|','0A'x);
    if not (date=. and json=' ') then output;
  end;
run;

Re: Parse and reformat JSON data

Tom — Wed, 16 Aug 2023 18:27:47 GMT

Once you have the JSON data properly extracted from the CSV file you can finally turn to trying to parse it.

data tall;
  set json(keep=id type rep json);
  where json ne ' ';
  length line row 8 name $32 value $200 term $300 record $1500;
  do line=1 to countw(json,'|');
     record=left(scan(json,line,'|'));
     do row=1 by 1 until(term=' ');
        term=scan(record,row,"[{,'}]"||'A0'x);
        term=translate(term,' ','A0'x);
        name=dequote(scan(term,1,':','q'));
        value=dequote(scan(term,2,':','q'));     
        if term ne ' ' then output;
    end;
  end;
  drop json term record;
run;

proc sort;
  by id type rep line row;
run;

proc transpose data=tall out=wide(drop=_name_) ;
  where name ne 'id';
  by id type rep line ;
  id name;
  var value;
run;

Looks like you also might need to do a little more cleaning. It appears some of the JSON strings have MID instead of MIDPOINT for one the timestamp variables.

Re: Parse and reformat JSON data

confooseddesi89 — Wed, 16 Aug 2023 22:35:34 GMT

Thanks, this worked perfectly!

I have one remaining issue: most of the variables are character; they should be numeric. I tried the "input" function but the datetime and date variables are throwing an error of "invalid argument at line XX column XX" - the structure of the datetime variables is YYYY-MM-DDTHH:MM:SSZ (e.g., 2021-08-17T06:46:30Z) and the date variable is YYYY-MM-DD (e.g., 2021-08-17). Can you provide any help or should I make another post for this issue? Thanks again!

Re: Parse and reformat JSON data

Tom — Thu, 17 Aug 2023 00:02:46 GMT

You can use iNPUT() to make a numeric variable from the character variable VALUE before transposing. Then use that variable in the PROC TRANSPOSE step instead.

Just use IF/THEN logic to use a different INFORMAT based on the variable type. Since you you have at least one DATE variable and a number of DATETIME variables you will need to use IF/THEN/ELSE logic to use the right informat.

32. will work for the normal numbers.

You might be able to use ANYDTDTE and ANYDTDTM for the others.

You will need to attach formats to the date and datetime variables in the resulting WIDE dataset so they print in a way that humans can understand.