DATA Step, Macro, Functions and more

how to separate various values from data string

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

how to separate various values from data string

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.

 


Accepted Solutions
Solution
‎04-20-2016 08:07 AM
Super User
Super User
Posts: 7,407

Re: how to separate various values from data string

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.

 

 

View solution in original post


All Replies
Super Contributor
Posts: 336

Re: how to separate various values from data string

[ Edited ]

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;

 

Solution
‎04-20-2016 08:07 AM
Super User
Super User
Posts: 7,407

Re: how to separate various values from data string

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.

 

 

New Contributor
Posts: 4

Re: how to separate various values from data string

Hi,

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

regards
LG
Super Contributor
Posts: 305

Re: how to separate various values from data string

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;

Super User
Posts: 9,682

Re: how to separate various values from data string

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.

New Contributor
Posts: 4

Re: how to separate various values from data string

Guys, 

 

I`ve got it ! Smiley Happy

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

 

wish you best Smiley Happy

 

regards

LG

Super Contributor
Posts: 305

Re: how to separate various values from data string

Please mark it as solved Smiley Wink

Super Contributor
Posts: 305

Re: how to separate various values from data string

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

New Contributor
Posts: 4

Re: how to separate various values from data string

no worries mate Smiley Happy

 

reg.

LG

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 380 views
  • 1 like
  • 5 in conversation