Desktop productivity for business analysts and programmers

Seperate Files in Hierarchical Delimiters

Accepted Solution Solved
Reply
SAS Employee
Posts: 4
Accepted Solution

Seperate Files in Hierarchical Delimiters

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

 


Accepted Solutions
Solution
‎10-30-2016 02:17 AM
Grand Advisor
Posts: 9,571

Re: Seperate Files in Hierarchical Delimiters

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,963

Re: Seperate Files in Hierarchical Delimiters

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.

SAS Employee
Posts: 4

Re: Seperate Files in Hierarchical Delimiters

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

Respected Advisor
Posts: 4,963

Re: Seperate Files in Hierarchical Delimiters

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.

SAS Employee
Posts: 4

Re: Seperate Files in Hierarchical Delimiters

[ Edited ]

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

Results.png

 

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 

Grand Advisor
Posts: 9,571

Re: Seperate Files in Hierarchical Delimiters

[ Edited ]
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;
SAS Employee
Posts: 4

Re: Seperate Files in Hierarchical Delimiters

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?

Solution
‎10-30-2016 02:17 AM
Grand Advisor
Posts: 9,571

Re: Seperate Files in Hierarchical Delimiters

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 416 views
  • 3 likes
  • 3 in conversation