Splitting Text into different columns with headings

[J_J_J]

Hello.

I have a variable like below:

VAR

--------

{"A123": "12", "B234": "-157.5", "C345": "1954"}

I need to split it into columns with headings:

A123  B234     C345

-------  --------   --------

12      -157.5   1954

Max number of variables is 50

Please help to split it and to add the headings.

[RW9]

Is that from a Json file?  It looks a bit Json like.  There are already a fair few papers and help on this:

http://support.sas.com/resources/papers/proceedings17/0856-2017.pdf

https://blogs.sas.com/content/sasdummy/2016/12/02/json-libname-engine-sas/

If its not this, please specify where this string comes from, and why it is in that form as its not ideal.  You would then need further code to get it workable:

data have;
 length inter vname vvar $200;
 str='{"A123": "12", "B234": "-157.5", "C345": "1954"}';
 str=compress(str,'"{}');
 do i=1 to countw(str,',');
 inter=scan(str,i,',');
 vname=scan(inter,1,":");
 vvar=scan(inter,2,":");
 output;
 end;
run;

proc transpose data=have out=want;
 var vvar;
 id vname;
 idlabel vname;
run; 

And even that isn't very good as the numerics are not numeric etc.

[J_J_J]

Thank you RW9

Possible that it's from Json file, but I can't change it, because I use the ready table which I should process.  

I have many rows with variables in different order and with different variable count:

{"A123": "12", "B234": "-157.5", "C345": "1954"}

{"A123": "12", "C345": "-157.3", "d123": "1456"}

{"A123": "17", "B234": "-143.5", "C345": "1745", "D123": "1745"}

{"A123": "15", "B234": "-154.4", "C345": "998"}

[RW9]

Ah, there is that joyous word again, "can't".  

Anyway, the code I presented should work nontheless, although building a tower on bad foundations will eventually lead to total disaster.

[data_null__]

filename FT15f001 temp;
data pair;
   infile FT15F001 dlm='{}":, ';
   input name :$32. value :32. @@;
   parmcards;
{"A123": "12", "B234": "-157.5", "C345": "1954"}
{"A123": "12", "C345": "-157.3", "d123": "1456"}
{"A123": "17", "B234": "-143.5", "C345": "1745", "D123": "1745"}
{"A123": "15", "B234": "-154.4", "C345": "998"}
;;;;
   run;
proc print;
   run;

[J_J_J]

Thank you data_null_

I'm looking for solution which will allow not to use parmcards, datalines etc. where you have to specifies  data.

Ex:

{"A123": "12", "B234": "-157.5", "C345": "1954"}
{"A123": "12", "C345": "-157.3", "d123": "1456"}
{"A123": "17", "B234": "-143.5", "C345": "1745", "D123": "1745"}
{"A123": "15", "B234": "-154.4", "C345": "998"}

[data_null__]

Is the data in a file?

[J_J_J]

No, in the table on server

[data_null__]

data have;
   input line $80.;
   id + 1;
   cards;
{"A123": "12", "B234": "-157.5", "C345": "1954"}
{"A123": "12", "C345": "-157.3", "d123": "1456"}
{"A123": "17", "B234": "-143.5", "C345": "1745", "D123": "1745"}
{"A123": "15", "B234": "-154.4", "C345": "998"}
;;;;
   run;
filename FT15f001 temp;
data pair;
   infile FT15F001 dlm='{}":, ' missover;
   input @1 @;
   do while(not eof);
      set have end=eof;
      _infile_ = line;
      input @1 @;
      do while(1);
         input name :$upcase32. value :32. @;
         if missing(name) then leave;
         output;
         end;
      end;
   parmcards;
Necessary evil
;;;;
   run;
proc print;
   run;
proc transpose data=pair out=wide(drop=_name_);
   by id;
   id name;
   var value;
   run;
proc print;
   run;

[J_J_J]

data_null, thanks so much! You made my day!

[Ksharp · Accepted solution]

data have;
   input line $80.;
   id + 1;
   cards4;
{"A123": "12", "B234": "-157.5", "C345": "1954"}
{"A123": "12", "C345": "-157.3", "D123": "1456"}
{"A123": "17", "B234": "-143.5", "C345": "1745", "D123": "1745"}
{"A123": "15", "B234": "-154.4", "C345": "998"}
;;;;
   run;

data temp;
 set have;
 do i=1 to countw(strip(line),',{}');
  token=scan(line,i,',{}');
  name=dequote(strip(scan(token,1,':')));
  value=dequote(strip(scan(token,2,':')));
  output;
 end;
 keep id name value;
run;
proc sort data=temp;
by id;
run;
proc transpose data=temp out=want(drop=_:);
by id;
id name;
var value;
run;

proc print;run;

[J_J_J]

Ksharp, thank you!