topic Re: how to separate various values from data string in SAS Programming

how to separate various values from data string

lemongrass — Tue, 19 Apr 2016 07:30:56 GMT

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.

Re: how to separate various values from data string

user24feb — Tue, 19 Apr 2016 08:05:57 GMT

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;

Re: how to separate various values from data string

RW9 — Tue, 19 Apr 2016 08:34:05 GMT

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.

Re: how to separate various values from data string

lemongrass — Tue, 19 Apr 2016 08:44:57 GMT

Hi,

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

regards
LG

Re: how to separate various values from data string

Loko — Tue, 19 Apr 2016 08:57:13 GMT

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;

Re: how to separate various values from data string

Ksharp — Tue, 19 Apr 2016 09:28:53 GMT

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.

Re: how to separate various values from data string

lemongrass — Wed, 20 Apr 2016 07:38:15 GMT

Guys,

I`ve got it ! 🙂

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

wish you best 🙂

regards

Re: how to separate various values from data string

Loko — Wed, 20 Apr 2016 07:50:57 GMT

Please mark it as solved 😉

Re: how to separate various values from data string

Loko — Wed, 20 Apr 2016 09:52:00 GMT

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

Re: how to separate various values from data string

lemongrass — Wed, 20 Apr 2016 12:14:49 GMT

no worries mate 🙂

reg.