ooo ooo - 2 months ago 6x
JSON Question

How to concatenate formatted values in SAS

I'm exporting data in JSON format with sas using this macro I made:

%macro json4datatables(ds,path,file,charvars,numvars)
/ store source

/* creates a json with no headers
* a bit like a csv without the first line
* it takes thus less space
* but you have to know which column is what

data _null_;
length line $300;
set &ds nobs=nobs end=end;
file "&path.&file." encoding='utf-8' bom/**/ ;

line = '[';

%if &charvars ne %then %do;
%do i=1 %to %sysfunc(countw(&charvars));
%let charvar = %scan(&charvars, &i);
%if &i ne 1 %then %do;
line = cats(line,',');
line = cats(line,'"',&charvar,'"');
%if &numvars ne %then %do;
%do i=1 %to %sysfunc(countw(&numvars));
%let numvar = %scan(&numvars, &i);
%if &i ne 1 OR &charvars ne %then %do;
line = cats(line,',');
line = cats(line,'',&numvar,'');

line = cats(line,']');

if _n_=1 then put '{"data": [';
if not end then put line +(-1) ',';
else do;
put line;
put ']}';

%mend json4datatables;

but my problem is that raw values are exported.

I would like to export the formatted values.

How can I achieve this?

I'm thinking maybe there is a function that allows to concatenate formatted values instead of values and I could replace cats() with it.


Tom Tom

Use the VVALUE() function.

line = cats(line,'',vvalue(&numvar),'');

Also why not just use the CATX() function? Replace

%if &i ne 1 OR &charvars ne %then %do;
    line = cats(line,',');
line = cats(line,'',vvalue(&numvar),'');


line = catx(',',line,vvalue(&numvar));

For the character values use the QUOTE() function.

line = catx(',',line,quote(cats(vvalue(&charvar))));