BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Varma8
SAS Employee

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
Astounding
PROC Star

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
SAS Employee

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
PROC Star

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
SAS Employee

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 

Ksharp
Super User
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
SAS Employee

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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