BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
J_J_J
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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__
Jade | Level 19
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;

 

Capture.PNG

J_J_J
Obsidian | Level 7

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__
Jade | Level 19
Is the data in a file?
J_J_J
Obsidian | Level 7

No, in the table on server

data_null__
Jade | Level 19
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;

Capture.PNG

 

J_J_J
Obsidian | Level 7

data_null, thanks so much! You made my day!

Ksharp
Super User
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
Obsidian | Level 7

Ksharp, thank you!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 2463 views
  • 0 likes
  • 4 in conversation