topic Seperate Files in Hierarchical Delimiters in SAS Enterprise Guide

Seperate Files in Hierarchical Delimiters

Varma8 — Tue, 25 Oct 2016 03:01:41 GMT

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

Re: Seperate Files in Hierarchical Delimiters

Astounding — Tue, 25 Oct 2016 05:39:48 GMT

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.

Re: Seperate Files in Hierarchical Delimiters

Varma8 — Tue, 25 Oct 2016 05:50:30 GMT

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

Re: Seperate Files in Hierarchical Delimiters

Astounding — Tue, 25 Oct 2016 05:57:36 GMT

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.

Re: Seperate Files in Hierarchical Delimiters

Varma8 — Tue, 25 Oct 2016 06:07:22 GMT

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

Re: Seperate Files in Hierarchical Delimiters

Ksharp — Tue, 25 Oct 2016 08:38:47 GMT

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;

Re: Seperate Files in Hierarchical Delimiters

Varma8 — Wed, 26 Oct 2016 09:04:01 GMT

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?

Re: Seperate Files in Hierarchical Delimiters

Ksharp — Wed, 26 Oct 2016 10:21:38 GMT

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;