how to transpose data with multiple occurrences in sas

Question

I have a 2 column dataset - accounts and attributes, where there are 6 types of attributes. I am trying to use PROC TRANSPOSE in order to set the 6 different attributes as 6 new columns and set 1 where the column has that attribute and 0 where it doesn't

Answer 1

This answer shows two approaches:

• Proc TRANSPOSE, and
• array based transposition using index lookup via hash.

For the case that all of the accounts missing the same attribute, there would be no way for the data itself to exhibit all the attributes -- ideally the allowed or expected attributes should be listed in a separate table as part of your data reshaping.

Proc TRANSPOSE

When working with a table of only account and attribute you will need to construct a view adding a numeric variable that can be transposed. After TRANSPOSE the result data will have to be further massaged, replacing missing values (.) with 0.

Example:

data have;
  call streaminit(123);
  do account = 1 to 10;
    do attribute = 'a','b','c','d','e','f';
      if rand('uniform') < 0.75 then output;
    end;
  end;
run;

data stage / view=stage;
  set have;
  num = 1;
run;

proc transpose data=stage out=want;
  by account;
  id attribute;
  var num;
run;

data want;
  set want;
  array attrs _numeric_;
  do index = 1 to dim(attrs);
    if missing(attrs(index)) then attrs(index) = 0;
  end;
  drop index;
run;

proc sql;
  drop view stage;

From

To

Advanced technique - Array and Hash mapping

In some cases the Proc TRANSPOSE is deemed unusable by the coder or operator, perhaps very many by groups and very many attributes. An alternate way to transpose attribute values into like named flag variables is to code:

• Two scans
  • Scan 1 determine attribute values that will be encountered and used as column names
    • Store list of values in a macro variable
  • Scan 2
    • Arrayify the attribute values as variable names
    • Map values to array index using hash (or custom informat per @Joe)
    • Process each group. Set arrayed variable corresponding to each encountered attribute value to 1.  Array index obtained via lookup through hash map.

Example:

* pass #1, determine attribute values present in data, the values will become column names;
proc sql noprint; 
  select distinct attribute into :attrs separated by ' ' from have;

* or make list of attributes from table of attributes (if such a table exists outside of 'have');
*  select distinct attribute into :attrs separated by ' ' from attributes;

%put NOTE: &=attrs;

* pass #2, perform array based tranposformation;

data want2(drop=attribute);
  * prep pdv, promulgate by group variable attributes;
  if 0 then set have(keep=account);

  array attrs &attrs.;
  format &attrs. 4.;

  if _n_=1 then do;
    declare hash attrmap();
    attrmap.defineKey('attribute');
    attrmap.defineData('_n_');
    attrmap.defineDone();
    do _n_ = 1 to dim(attrs);
      attrmap.add(key:vname(attrs(_n_)), data: _n_);
    end;
  end;

  * preset all flags to zero;

  do _n_ = 1 to dim(attrs);
    attrs(_n_) = 0;
  end;

  * DOW loop over by group;

  do until (last.account);
    set have;
    by account;

    attrmap.find();     * lookup array index for attribute as column;
    attrs(_n_) = 1;     * set flag for attribute (as column);
  end;

  * implicit output one row per by group;
run;