how to separate various values from data string

[lemongrass]

Hi Guys !

Please help me to solve below issue:

I`ve got data string like this  :  {"labelX":"valueX","labelY":"valueY","labelZ":"valueZ"}

I need to split data by comma, where "label" should be the name of column, and rows should be filled with "value"

thanks in advance

regards

LG.

[user24feb]

There are a couple of possibilities. Try:

Data A;

Txt=%Unquote('{"labelX":"valueX","labelY":"valueY","labelZ":"valueZ"');

Output;

Run;

Data B (Keep=Name Value);

Set A;

Txt=Compress(Txt,'"[]'); * please replace with squiggly brackets - I can't get them into the editor here;

Txt=Translate(Txt,',',':');

Do i=1 To Count(Txt,',') By 2;

Name=Scan(Txt,i,',');

Value=Scan(Txt,i+1,',');

Output;

End;

Run;

Proc Transpose =B Out=B_T (Drop=_NAME_);

ID Name;

Var Value;

Run;

[RW9 · Accepted solution]

Well, to be honest that looks a bit Json like to me.  Is it only one string, if so then something like:

data have;
  txt='{"labelX":"valueX","labelY":"valueY","labelZ":"valueZ"}';
  call execute('data want;');
  do i=1 to countw(txt,",");
    call execute(cat(" length ",compress(scan(scan(txt,i,","),1,":"),'"{}')," $100; ",compress(scan(scan(txt,i,","),1,":"),'"{}'),'="',compress(scan(scan(txt,i,","),2,":"),'"{}'),'";'));
  end;
  call execute('run;');
run;

Is probably the quickest way to go.  Though I suspect we are only seeing the tiny bit of the picture here.  Why do you have that type of string, where does it come from?  More information would provide a better solution.

[lemongrass]

Hi,

it comes from IT app LOG FILE, and for that moment it`s looks like that 😄

regards
LG

[Loko]

hello,

data have;
x='{"labelX":"valueX","labelY":"valueY","labelZ":"valueZ"}';
x=prxchange('s/{|}|"//i',-1,x);
x=prxchange('s/:/="/i',-1,x);
x=prxchange('s/,/";/i',-1,x);

call symput ('syn',strip(x)||'";');
run;

data want;
&syn;
run;

[Ksharp]

Post a sample FILE ,and let us test it.

It is JSON file . and PROC DS2  has JSON package, maybe you should look at it.

[lemongrass]

Guys, 

I`ve got it ! 🙂

thank you All for quick help !!!  ( SCAN and easy loop was the solution )

wish you best 🙂

regards

LG

[Loko]

Please mark it as solved 😉

[Loko]

not my message...but the solution you have chosen...

[lemongrass]

no worries mate 🙂

reg.

LG