How to concatenate formatted values in SAS

Question

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

%macro json4datatables(ds,path,file,charvars,numvars)
    / store source
    DES="json4datatables(ds,path,file,charvars,numvars)";

    /* 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,',');
                %end;
                line = cats(line,'"',&charvar,'"');
            %end;
        %end;
        %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,',');
                %end;
                line = cats(line,'',&numvar,'');
            %end;
        %end;

        line = cats(line,']');

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

%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.

Thanks!

Tom · Accepted Answer · 2016-08-11T14:12:50.640

3

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,',');
%end;
line = cats(line,'',vvalue(&numvar),'');

with

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

For the character values use the QUOTE() function.

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

Move the addition of the square brackets to the end.

line = cats('[',line,']');

edited Aug 11 '16 at 14:12

answered Aug 11 '16 at 12:38

Tom

47,574
2
16
29

Thanks! and I'm used to cats, it makes things more readable for me, but i'll try. – stallingOne Aug 11 '16 at 12:55
the catx replacements do not work, they add a comma in front of the first value `[,"BORPER","fatal_1","0.78"],` – stallingOne Aug 11 '16 at 13:21
1

Because the LINE variable was not empty. You could build the comma separated values first and then add the square brackets later. – Tom Aug 11 '16 at 14:00

score 2 · Answer 2 · answered Aug 11 '16 at 12:36

2

VVALUE is probably the function you are looking for, but it would not replace CATS. Also for quoting use QUOTE function.

answered Aug 11 '16 at 12:36

data _null_

8,534
12
14

score 0 · Answer 3 · answered Aug 11 '16 at 12:41

0

You can use put, putc or putn function to print value with format, to obtain a format use vformat function, but when you are using vformat function only putn and putc support it.

line = cats(line,'"',putc(&charvar, vformat(&charvar)),'"');
line = cats(line,'',putn(&numvar, vformat(&numvar)),'');

answered Aug 11 '16 at 12:41

Robert Soszyński

1,178
1
7
12

The problem with using the `put` functions is that you need to know the format name. Using `vvalue()` as the others suggested will take the format name applied to the column and use that instead. – Robert Penridge Aug 11 '16 at 14:38

How to concatenate formatted values in SAS

3 Answers3