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.
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;
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.
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"}
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.
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;
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"}
No, in the table on server
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;
data_null, thanks so much! You made my day!
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;
Ksharp, thank you!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.