topic Re: Splitting Text into different columns with headings in SAS Procedures

Splitting Text into different columns with headings

J_J_J — Mon, 17 Sep 2018 07:35:24 GMT

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.

Re: Splitting Text into different columns with headings

RW9 — Mon, 17 Sep 2018 08:03:08 GMT

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.

Re: Splitting Text into different columns with headings

J_J_J — Mon, 17 Sep 2018 09:13:19 GMT

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"}

Re: Splitting Text into different columns with headings

RW9 — Mon, 17 Sep 2018 09:18:07 GMT

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.

Re: Splitting Text into different columns with headings

data_null__ — Mon, 17 Sep 2018 11:00:44 GMT

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;

Re: Splitting Text into different columns with headings

J_J_J — Mon, 17 Sep 2018 11:21:30 GMT

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"}

Re: Splitting Text into different columns with headings

data_null__ — Mon, 17 Sep 2018 11:29:31 GMT

Is the data in a file?

Re: Splitting Text into different columns with headings

J_J_J — Mon, 17 Sep 2018 11:42:12 GMT

No, in the table on server

Re: Splitting Text into different columns with headings

data_null__ — Mon, 17 Sep 2018 11:54:34 GMT

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;

Re: Splitting Text into different columns with headings

Ksharp — Mon, 17 Sep 2018 13:01:57 GMT

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;

Re: Splitting Text into different columns with headings

J_J_J — Tue, 18 Sep 2018 05:16:02 GMT

data_null, thanks so much! You made my day!

Re: Splitting Text into different columns with headings

J_J_J — Tue, 18 Sep 2018 05:16:37 GMT

Ksharp, thank you!