Summing a table with an unknown number of variables?

Question

I'm fairly new with SAS. I've used it a bit in the past but am really rusty.

I've got a table that looks like this:

Key   Group1 Metric1  Group2  Metric2  Group3 Metric3
1                .       r       20             .
1                .                .        t     3

For several unique keys. I want everything to appear on one row so it looks like.

Key   Group1 Metric1  Group2  Metric2  Group3 Metric3
1                .       r       20       t      3

Another wrinkle is I don't know how many group and metric columns I'll have (although I'll always have the same number).

I'm not sure how to approach this. I'm able to get a list of column names and use them in a macro, I'm just not sure what proc or datastep function I need to use to collapse everything down. I would be extremely greatful for any suggestions.

Answer 1

There's a very simple way to do this using a nice trick. I've answered similar questions on this before, see here for one of them. This should achieve exactly what you're after.

Answer 2

You can use 2 temporary arrays (one for the character variables, and another for the numeric), and fill them with the non-blank values accordingly. When you reach last.key, you can load the temporary arrays back into the source variables.

If you know the maximum length of the character variables in advance, you can hard code it, but if not you can determine it dynamically.

This assumes that for each key, each variable is only populated once. Otherwise it will take the last value it sees for a particular variable within each key.

%LET LIB    = work ;
%LET DSN    = mydata ;
%LET KEYVAR = key ;

/* Get column name/type/max length */
proc sql ;
  /* Numerics */
  select name, count(name) into :NVARNAMES separated by ' ', :NVARNUM
  from dictionary.columns
  where libname = upcase("&LIB")
    and memname = upcase("&DSN")
    and name   ^= upcase("&KEYVAR")
    and type    = 'num' ;

  /* Characters */
  select name, count(name), max(length) into :CVARNAMES separated by ' ', :CVARNUM, :CVARLEN
  from dictionary.columns
  where libname = upcase("&LIB")
    and memname = upcase("&DSN")
    and name   ^= upcase("&KEYVAR")
    and type    = 'char' ;
quit ;

data flatten ;
  set &LIB..&DSN ;
  by &KEYVAR ;

  array  n{&NVARNUM} &NVARNAMES ;
  array nt{&NVARNUM} _TEMPORARY_ ;

  array  c{&CVARNUM} &CVARNAMES ;
  array ct{&CVARNUM} $&CVARLEN.. _TEMPORARY_ ;

  retain nt ct ;
  if first.&KEYVAR then do ;
    call missing(of nt{*}, of ct{*}) ;
  end ;

  /* Load non-missing numeric values into temporary array */
  do i = 1 to dim(n) ;
    if not missing(n{i}) then nt{i} = n{i} ;
  end ;

  /* Load non-missing character values into temporary array */
  do i = 1 to dim(c) ;
    if not missing(c{i}) then ct{i} = c{i} ;
  end ;

  if last.&KEYVAR then do ;
    /* Load numeric back into original variables */
    call missing(of n{*}) ;
    do i = 1 to dim(n) ;
      n{i} = nt{i} ;
    end ;

    /* Load character back into original variables */
    call missing(of c{*}) ;
    do i = 1 to dim(c) ;
      c{i} = ct{i} ;
    end ;
    output ;
  end ;

  drop i ;
run ;