How to extract some value from character column in PROC SQL in SAS Enterprise Guide?

I have table in SAS like below:

data type:

• ID – numeric

  MEDevel.com: Open-source for Healthcare and Education

  Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

  Visit Medevel

• TYPE – character

  ID	TYPE
  111	{"Top":10}
  222	{"abC":"false","Top":111, "aa":5}
  333	{"Top":15.2}
  …	…

And I need to create new column "COL1" base on column "TYPE" where I need to extract value which is after character "Top":

So I need something like below:

ID  | TYPE                              | COL1
--- |-----------------------------------|------
111 | {"Top":10}                        | 10
222 | {"abC":"false","Top":111, "aa":5} | 111
333 | {"Top":15.2}                      | 15.2
... | ...                               | ...

How can I do that in PROC SQL in SAS Enterprise Guide ?

>Solution :

Perhaps use the following

data want;
   set have;
   length col1 $50.;
   col1 = strip(scan(tranwrd(type,'"Top":','|'),2,'|},'));
run;

• Use the tranwrd() function to replace all occurrences of "Top": in type by a vertical bar |.
• Use the scan() function to extract the substring between the vertical bar | and the closing bracket } or comma ,.

 id                    type                    col1
 111     {"Top":10}                              10
 222     {"abC":"false","Top":111,"aa":5}       111
 333     {"Top":15.2}                          15.2