Seperate Files in Hierarchical Delimiters

[Varma8]

Hi All,

I'm having quite a bit of trouble prepping the data. It's a text file. Trying to workout it out using SAS Enterprise Guide

the input is such

{"AI0":[-1755,-4320,-6965,-4770,-2053,-4515,-6242,-1785,1766,10582],"AI1":[582,1002,1118,-625,-2439]},{"AI0":[496,-3393,-2131,-7,-909,-1164,1272],"AI1":[1642,-462,-1147,224,13,49,696,-985]}

I wish returned a result as such

No             AI0         AI1

1              -1755       582

1              -4320       1002

until the end of row 1 and continue with row 2

2               496          1642

2              -3393        -462

[Astounding]

This task will be reasonably complex.  I'm not sure that you can do it using only the prepackaged capabilities of Enterprise Guide.  You will likely need some custom code.

To get started, you will need to sketch out the result you want in more detail.  You have a different number of values for AI0 vs. AI1 in the incoming data.  Which ones do you use and which do you discard?  How do they match up?  How do you determine the value of NO?  For your sample input data, you will need to sketch out the entire result not just a representative sample.

[Varma8]

Hi Astounding,

Thanks for the reply. I was wondering the same too that if I'm able to perform that thru EG without writing any codes.

Lets assume that AI0 and AI1 are two sensors submitting its values. Every second, it transmits hundreds of inputs. The { } represents one second as in the data submitted by sensors. {"AI0":[-1755,-4320,-6965,-4770,-2053,-4515,-6242,-1785,1766,10582],"AI1":[582,1002,1118,-625,-2439]} means that in second 1, AI0 submitted -1755 till 10582 and AI1 submitted the values 582 till -2439.

And the next {"AI0":[496,-3393,-2131,-7,-909,-1164,1272],"AI1":[1642,-462,-1147,224,13,49,696,-985]} means data from the sensors at second 2. 

The data comes in a ASC file and thus the Delimiter for the seconds are {} and for the sensors are []

I would like to arrange the data thru EG in this format

Second        AI0       AI1          

1              -1755       582

1              -4320      1002

1               6965      1118

...                ...           ....

2               496        1642

2              -3393      -462

Hope you could enlighten me

I know it could be a tedious process

[Astounding]

The details are important here.  You will need to sketch out the complete final result (for the limited amount of data that you have already typed in.  A sample with "..." included will not be sufficient.

There are questions about how to line up the data when each sensor sends a different number of measurements per second.  So you will need to define the final result in more detail.

No programming is required at this point, but a crystal clear picture of the final outcome is mandatory.

[Varma8]

I would like to display the aggregate of the data of each second of the result 

The date time column is what I mentioned as seconds earlier. As you can see, I to find the average of the values of each of the seconds of each sensors. From the data I've given, the first two rows of result will be the output. 15:14 and 15:15

Thanks 

[Ksharp]

options noquotelenmax;
data have;
  infile "/folders/myfolders/SeriesDataOut.txt" lrecl=200000 dlm='}{';
  input STR : $800. @@;
  if _N_=1 then 
    call execute ('data temp; length  AI0 AI1 $ 200;');
  if STR=:'"AI0"' then do;
    STR=translate(STR,'""','[]' ); 
    STR=tranwrd(STR,'","','";"');
    STR=tranwrd(STR,'":"','"n="');
    call execute(cats(STR,';n+1;output;'));
  end;  
run;

data want;
 set temp;
 do i=1 to max(countw(AI0,','),countw(AI1,','));
  _AI0=input(scan(AI0,i,','),best32.);
  _AI1=input(scan(AI1,i,','),best32.);
  output;
 end;
 keep _: n;
run;

[Varma8]

Hi KSharp,

The code works perfectly with the given string. But, my data has string longer than 32767

call execute ('data temp; length  AI0 AI1 $ 32767;');

the cmd length is limiting the character read to just 32767. My data string is around 40000 as in {"AI0":[-1755,-4320,-6965,-4770,-2053,-4515,-6242,-1785,1766,10582],"AI1":[582,1002,1118,-625,-2439]} is longer than 32767

Is there any workaround for this request. Maybe a different call function?

[Ksharp · Accepted solution]

OK. It looks like you need more code.

data have(where=(str is not missing));
  infile "/folders/myfolders/SeriesDataOut.txt" dsd lrecl=2000000 dlm='}{[],:';
  input STR : $100. @@;
  length name $ 100;
  retain name;
  if upcase(str) =: 'AI' then do;name=str;delete;end;
 run;
proc transpose data=have out=temp(drop=_name_);
 by name notsorted;
 var str;
run;
data temp;
 set temp;
 if name='AI0' then n+1;
run;
proc transpose data=temp out=want(drop=_name_);
 by n;
 id name;
 var col:;
run;