topic Re: How to use extact data , when macro variable has special characters in SAS Programming

How to use extact data , when macro variable has special characters

learn_SAS_23 — Wed, 29 Jan 2025 15:26:36 GMT

Hello team ,

i have a data in macro variable like

%let party_data = {"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"};

how to store the content of name and id in two separate macro variables

am trying to use below code , but it is not working as expected

i would like to store as

party_name =as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`

party_id=121212-1212

%let party_data = {"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"};

party=compress(tranwrd(party_data,'","','¤'),'"{}[]');   
party_name=substr(kscan(party,1,'¤'),6);
party_id=substr(kscan(party,2,'¤'),4);

 
 
 output : 

 party   =  name:as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\#¤%&/()=?@£$€\\~|<>_:;*^`¤id:121212-1212
party_name = as<br>df:asfd;asdf*asdfâsdf¨¨	
party_id = ()=?@£$€\\~|<>_:;*^`

Re: How to use extact data , when macro variable has special characters

Tom — Wed, 29 Jan 2025 16:31:48 GMT

You have unbalanced quotes. Perhaps because the string is trying to use the unix shell notation of treating \ as an escape character to mask the extra " and \ characters.

You can use %bquote() to store values with unbalanced quotes.

And then to WORK with the value of the macro variable PARTY_DATA in your other SAS code you could use the SYMGET() function to retrieve its value.

 1          %let party_data = %bquote({"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;
 1        ! *^`","id":"121212-1212"});
 2          %put &=party_data ;
 PARTY_DATA={"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"}
 3          
 4          data test;
 5            party_data = symget('party_data');
 6            party=compress(tranwrd(party_data,'","','¤'),'"{}[]');
 7            party_name=substr(kscan(party,1,'¤'),6);
 8            party_id=substr(kscan(party,2,'¤'),4);
 9            put (party:) (=/);
 10         run;
 
 party_data={"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"}
 party=name:as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\#¤%&/()=?@£$€\\~|<>_:;*^`¤id:121212-1212
 party_name=as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\#
 party_id=()=?@£$€\\~|<>_:;*^`
 NOTE: The data set WORK.TEST has 1 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

Instead of symget() you might use %sysfunc() to call quote() to convert the macro variable into a string that SAS will recognize as a string literal.

%let party_data = %bquote({"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"});
%put &=party_data ;
%let party_data = %sysfunc(quote(&party_data,%str(%')));

data test;
  party_data =&party_data;
  party=compress(tranwrd(party_data,'","','¤'),'"{}[]');   
  party_name=substr(kscan(party,1,'¤'),6);
  party_id=substr(kscan(party,2,'¤'),4);
  put (party:) (=/);
run;

Re: How to use extact data , when macro variable has special characters

learn_SAS_23 — Wed, 29 Jan 2025 18:31:35 GMT

Sorry i would like to store as

party_name =as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`

party_id=121212-1212

Re: How to use extact data , when macro variable has special characters

Tom — Wed, 29 Jan 2025 18:38:36 GMT

@learn_SAS_23 wrote:

Sorry i would like to store as

party_name =as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`

party_id=121212-1212

I don't know what you mean by this comment.

Are you saying you don't know how to type in those values? Do you need them in macro variables? Or as actual data in variables in a SAS dataset?

Or are you saying you already have a string in a dataset variable and you want to extract those substrings from it?

Re: How to use extact data , when macro variable has special characters

learn_SAS_23 — Wed, 29 Jan 2025 18:56:46 GMT

With above code you provided  am getting result as

party_data={"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`","id":"121212-1212"}
party=name:as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\#¤%&/()=?@£$€\\~|<>_:;*^`¤id:121212-1212
party_name=as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\#
party_id=()=?@£$€\\~|<>_:;*^`


Instead i would like to get result as  , which needs to store in a macro variable

party_name=as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$€{[]}\\~|<>_:;*^`
party_id=121212-1212

can you help in achieving this

Re: How to use extact data , when macro variable has special characters

Tom — Wed, 29 Jan 2025 19:47:26 GMT

Ok. So the question is about how to PARSE json text.

I have had good luck using SCAN(). Seems to handle most simple JSON strings. Use = , [] and {} as delimiters and make sure to use the Q modifier.

But if the strings can contain " that JSON wants to use \ to hide you might need to first convert the \" into "" so that they are balanced. Remember to convert back again later.

And since the values might have unbalanced quotes remember to add macro quoting to the macro variable (or store it as a string quoted with single quotes).

%let party_data = %bquote({"name":"as<br>df:asfd;asdf*asdfâsdf¨¨asdf*asdf'´´½!\"#¤%&/()=?@£$\u20ac{[]}\\~|<>_:;*^`","id":"121212-1212"});

data _null_;
  length party_data $200 ;
  party_data = symget('party_data');
  party_data = tranwrd(party_data,'\"','""');
  length name $100 string $200 ;
  do i=1 by 2 until(name=' ');
    name=dequote(strip(scan(party_data,i,'{}:[],','q')));
    string=dequote(strip(scan(party_data,i+1,'{}:[],','q')));
    string = tranwrd(string,'"','\"');
    put name= / string= ;
    if name ne ' ' then do;
       name = cats('party_',name);
       call symputx(name,string);
       call execute(catx(' ','%nrstr(%let)',name,'=%nrstr(%superq)(',name,');'));
    end;
  end;
run;
%put &=party_id &=party_name ;

Re: How to use extact data , when macro variable has special characters

learn_SAS_23 — Wed, 29 Jan 2025 20:14:34 GMT

Thanks So much for your help , the above solution works perfectly ,

similarly i have another content in XML , where one of its attribute has value in below format

subparty = [{"name":"SUKUNIMI -11073533 ETUNIMI -11073533","id":"300576-456P","Code":"507808;test bank1","bNumber":"234234234234"},{"name":"TESTIYKS ACECGGHD","id":"010140-463P","Code":"529108;test bank2","bNumber":"324234234234"},{"name":"ARAHPE AÄLKOSKEÅÅÅ","id":"010153-111P","Code":"512404;test bank3","bNumber":"234234234234"}]

i would like to count all the subparty ,in above example we have 4 subparty 
and i need to store the values in a table with different columns like as below 

name1=SUKUNIMI -11073533 ETUNIMI -11073533
id1=300576-456P
Code1=507808;test bank1
bNumber1=234234234234

name2=TESTIYKS ACECGGHD
id2=010140-463P
Code1=529108;test bank2
bNumber2=324234234234

and so on ...

Re: How to use extact data , when macro variable has special characters

learn_SAS_23 — Wed, 29 Jan 2025 20:15:37 GMT

as stated above name may contain different special characters and unicode characters as well

Re: How to use extact data , when macro variable has special characters

Tom — Wed, 29 Jan 2025 20:49:38 GMT

Not much use to but such things into macro variables. Leave them in character variables where the special characters don't cause issues.

You could try the same SCAN() trick if you have the string in a character variable.

data test;

subparty = '
[{"name":"SUKUNIMI -11073533 ETUNIMI -11073533"
 ,"id":"300576-456P"
 ,"Code":"507808;test bank1"
 ,"bNumber":"234234234234"}
,{"name":"TESTIYKS ACECGGHD"
 ,"id":"010140-463P"
 ,"Code":"529108;test bank2"
 ,"bNumber":"324234234234"}
,{"name":"ARAHPE AÄLKOSKEÅÅÅ"
 ,"id":"010153-111P"
 ,"Code":"512404;test bank3"
 ,"bNumber":"234234234234"
 }
]'
;
length row 8 name $32 value $200 ;
do i=1 to 100 by 2 ;
  name=dequote(strip(scan(subparty,i,'[]{}:,','q')));
  value=dequote(strip(scan(subparty,i+1,'[]{}:,','q')));
  if name=' ' then leave;
  row+(name='name');
  output;
end;

drop i subparty;
run;

proc print;
run;

proc transpose out=wide(drop=_name_);
  by row;
  id name;
  var value;
run;

proc print;
run;

But since that text looks like a fully formed JSON file you could just put it in a file and then use the JSON libref engine to parse it for you.

options parmcards=json;
filename json temp;
parmcards4;
[{"name":"SUKUNIMI -11073533 ETUNIMI -11073533"
 ,"id":"300576-456P"
 ,"Code":"507808;test bank1"
 ,"bNumber":"234234234234"}
,{"name":"TESTIYKS ACECGGHD"
 ,"id":"010140-463P"
 ,"Code":"529108;test bank2"
 ,"bNumber":"324234234234"}
,{"name":"ARAHPE AÄLKOSKEÅÅÅ"
 ,"id":"010153-111P"
 ,"Code":"512404;test bank3"
 ,"bNumber":"234234234234"
 }
]
;;;;

libname json json ;
proc print data=json.root;
run;

Re: How to use extact data , when macro variable has special characters

Ksharp — Thu, 30 Jan 2025 02:35:07 GMT

As Tom said using JSON engine, since your data so looks like a JSON file.

%let subparty = [{"name":"SUKUNIMI -11073533 ETUNIMI -11073533","id":"300576-456P","Code":"507808;test bank1","bNumber":"234234234234"},{"name":"TESTIYKS ACECGGHD","id":"010140-463P","Code":"529108;test bank2","bNumber":"324234234234"},{"name":"ARAHPE AÄLKOSKEÅÅÅ","id":"010153-111P","Code":"512404;test bank3","bNumber":"234234234234"}]  ;
filename x temp;
data _null_;
 file x;
 length x $ 32767;
 x="%bquote(&subparty.)";
 put x;
run;
libname j json fileref=x;
proc copy in=j out=work;
run;
data _null_;
 set alldata;
 if p1='name' then n+1;
 call symputx(cats(p1,n),value);
run;

%put _user_;

77
78   %put _user_;
GLOBAL BNUMBER1 234234234234
GLOBAL BNUMBER2 324234234234
GLOBAL BNUMBER3 234234234234
GLOBAL BNUMBER4 234234234234
GLOBAL CODE1 507808;test bank1
GLOBAL CODE2 529108;test bank2
GLOBAL CODE3 512404;test bank3
GLOBAL ID1 300576-456P
GLOBAL ID2 010140-463P
GLOBAL ID3 010153-111P
GLOBAL J_JADP1LEN 7
GLOBAL J_JADPNUM 1
GLOBAL J_JADVLEN 36
GLOBAL NAME1 SUKUNIMI -11073533 ETUNIMI -11073533
GLOBAL NAME2 TESTIYKS ACECGGHD
GLOBAL NAME3 ARAHPE A?LKOSKE???

Re: How to use extact data , when macro variable has special characters

learn_SAS_23 — Thu, 30 Jan 2025 15:41:38 GMT

Thanks Tom and Ksharp for your answers , As you suggested seems the JSON method pointed by Ksharp will fit for my needs