CODEWITHSUNDEEP
sas
Gabrielius K
Gabrielius K

Reputation: 31

Make macro variables name as a value

I'm slicing data with proc SQL and group by for a report. Let's say my data consists of

  1. animal type - (cat, dog, etc.),
  2. year born,
  3. stats like average height, weight, and age,
  4. bins like hair color, country of birth, etc.

I'll make a macro to slice the data by one of the (4) bins, animal type and get the averages of height, weight, etc. Then I'd like to stack all of them into one table, for that, I need the data to have a name column, to see what it was grouped by, say name = hair color or country of birth.

%macro slice(data, group);
proc sql;

        create table &data._grpuped_by_&group.
        as select 
            animal_type,
            year_born,
            &group as group,
            '&group.' as name, /* this is the part i can't manage */
            avg(heigth) as avg_height
        from &data
        group by 1, 2, 3, 4
    ;
    quit;  

%mend slice;

Let's say I want my data to be grouped by hair color and I would like to have a column that indicates that the data was sliced by that variable.

What I get is "&group as a group" all the hair colors in a column named "name" exactly like the column "group". What I would like to get is a column named "name" with variable hair color.

Upvotes: 2

Views: 173

Answers (3)

Richard
Richard

Reputation: 27498

Proc TABULATE with an OUT= can capture all the groupings and continuous measures group-wise statistics in one step.

Example:

ods noresults;

%let GROUP_VARS = origin make type drivetrain enginesize;
%let STAT_VARS = msrp invoice weight length;
%let STATS = mean min;

proc tabulate data=sashelp.cars out=crossings ;
  class &GROUP_VARS;
  var msrp invoice weight length;
  table
    (&GROUP_VARS)
    ,
    (&STAT_VARS) * (&STATS) N
    ;
run;

ods results;

data want;
  length group_var $32 group_val $50;
  set crossings;
  if countc(_type_,'1') = 1 then do;
    group_var = scan("&GROUP_VARS", index(_type_,"1"));
    group_val = vvaluex(group_var);
  end;
run;

Upvotes: 0

Reeza
Reeza

Reputation: 21264

A PROC MEANS approach is here, no macros needed. 

    *variable list;
    %let var= sex age;

    *summary statistics;
    ods select none;
    proc means data=sashelp.class stackods n mean min median max;
        class &var. / mlf;
        ways 1;
        var weight height;
        ods output summary=want1;
    run;
    ods select all;

    data want2;
        set want1;
        *list of variables;
        array _v(*) $  &var.;

        *clean up to get variable name and value into single columns;
        do i=1 to dim(_v);

            if not missing(_v(i)) then
                do;
                    variableName=vname(_v(i));
                    variableValue=_v(i);
                end;
        end;
    run;

    proc print data=want2;
    var variable: N Mean Min Median Max;
    run;

Results in:

Obs Variable    variableName    variableValue   N   Mean    Min Median  Max
1   Weight  Age 11  2   67.750000   50.500000   67.750000   85.000000
2   Height  Age 11  2   54.400000   51.300000   54.400000   57.500000
3   Weight  Age 12  5   94.400000   77.000000   84.500000   128.000000
4   Height  Age 12  5   59.440000   56.300000   59.000000   64.800000
5   Weight  Age 13  3   88.666667   84.000000   84.000000   98.000000
6   Height  Age 13  3   61.433333   56.500000   62.500000   65.300000
7   Weight  Age 14  4   101.875000  90.000000   102.500000  112.500000
8   Height  Age 14  4   64.900000   62.800000   63.900000   69.000000
9   Weight  Age 15  4   117.375000  112.000000  112.250000  133.000000
10  Height  Age 15  4   65.625000   62.500000   66.500000   67.000000
11  Weight  Age 16  1   150.000000  150.000000  150.000000  150.000000
12  Height  Age 16  1   72.000000   72.000000   72.000000   72.000000
13  Weight  Sex F   9   90.111111   50.500000   90.000000   112.500000
14  Height  Sex F   9   60.588889   51.300000   62.500000   66.500000
15  Weight  Sex M   10  108.950000  83.000000   107.250000  150.000000
16  Height  Sex M   10  63.910000   57.300000   64.150000   72.000000

Upvotes: 1

Llex
Llex

Reputation: 1770

An error has occured cause of incorrect use of quoting macro variable. This article says:

Note: The title is enclosed in double quotation marks. In quoted strings in open code, the macro processor resolves macro variable references within double quotation marks but not within single quotation marks.

So, the correct solution: 

%macro slice(data, group); 
  proc sql;
    create table &data._grpuped_by_&group.
    as select 
        animal_type,
        year_born,
        &group as group,
        "&group." as name, /* here was the mistake, not '&group.' */
        avg(heigth) as avg_height
    from &data
    group by 1, 2, 3, 4
    ;
  quit;  
%mend slice;

Upvotes: 0

Related Questions