Read Column Into Row

From sasCommunity
Jump to: navigation, search

MWSUG.2015 Reading a Column into a Row to Count N-levels, Calculate Cardinality Ratio and Create Frequency and Summary Output In One Step

Programs

get-data-info.sas

%let dsid  = %sysfunc(open (&data     ));
%let n_obs = %sysfunc(attrn(&dsid,nobs));
%let rc    = %sysfunc(close(&dsid     ));
%put echo &=data &=n_obs;
%symdel dsid rc;
PROC contents data = &data;
title3 "data &data n-obs=&n_obs";
run;

read-column-into-row.sas

%put read-column-into-row beginning &=name &=n_obs;
 
DATA out_freq   (keep   = name value count percent)
     out_summary(keep   = name n_levels card_ratio n n_miss
                          mean std_dev median min max);
     attrib name       length =$32  value   length = 8
            count      length =  8  percent length = 8
            n_levels   length =  8
            card_ratio length =  8 label  =
           "cardinality ratio: n-levels/&n_obs"
            n          length =  8  n_miss  length = 8
            min        length =  8  max     length = 8
            mean       length =  8  median  length = 8
            std_dev    length =  8;
     array  _n(&n_obs) _temporary_;
     retain name "&name";
 
** read column into this row :: array;
do _i = 1 to &n_obs;
   *** avoid naming collisions by renaming;
   set &data (keep   =  &name
              rename = (&name = _value))
       point = _i;
   _n(_i) = _value;
   end;
 
**** sort values for counting n-levels;
call sortn(of _n(*));
 
** initialize with row.1 values;
value   = _n(1);* also: previous-value;
count    =    1;
percent  =    100*(count/&n_obs);
n_levels =    1;
 
** start counting n-levels from row.2;
do _i = 2 to &n_obs;
** if this-value ne previous-value;
   if _n(_i)     ne value then do;
      output out_freq;
      n_levels + 1;
      value = _n(_i);
      count  = 0;
      end;
   count   + 1;
   percent = 100*(count/&n_obs);
   end;
output out_freq;
 
card_ratio = n_levels / &n_obs;
 
** calculate summary.7 statistics;
n       = n     (of _n(*));* 7;
n_miss  = nmiss (of _n(*));* 7;
min     = min   (of _n(*));*5;
max     = max   (of _n(*));*5;
mean    = mean  (of _n(*));*5;
median  = median(of _n(*));*5;
std_dev = std   (of _n(*));*5;
output out_summary;
stop;
run;
PROC append data =  out_freq
            base = list_frequencies;
PROC append data =  out_summary
            base = list_summaries;
run;
%put read-column-into-row ending &=name;

proc-print-summaries.sas

PROC print data = list_summaries   noobs label;
           title4     'summaries'  ;
PROC print data = list_frequencies noobs;
           title4     'frequencies';
           by     name;
           id     name;
run;

demo-sashelp-class

demo-sashelp-class.sas

%let data = sashelp.class;
 
%include 'get-data-info.sas';
 
%let name    = age;
%include 'read-column-into-row.sas';
 
%let name    = height;
%include 'read-column-into-row.sas';
 
%let name    = weight;
%include 'read-column-into-row.sas';
 
%include 'proc-print-summaries.sas';

demo-sashelp-class.lst

Frequency and Summary in One Step
demo-sashelp-class
data sashelp.class n-obs=19

summaries
              cardinality
       n-       ratio:
name   levels n-levels/19  n    min  max     mean  median  std_dev
------ ------ -----------  --  ----  ---  -------  ------  -------
age       6      0.31579   19  11.0   16   13.316    13.0   1.4927
height   17      0.89474   19  51.3   72   62.337    62.8   5.1271
weight   15      0.78947   19  50.5  150  100.026    99.5  22.7739

Frequency and Summary in One Step
demo-sashelp-class
data sashelp.class n-obs=19
frequencies

name    value  count  percent
----    -----  -----  -------
age      11.0    2    10.5263
         12.0    5    26.3158
...
         16.0    1     5.2632


height   51.3    1     5.2632
         56.3    1     5.2632
...
         72.0    1     5.2632


weight   50.5    1     5.2632
         77.0    1     5.2632
...
        150.0    1     5.2632

References

--Ronald_J._Fehd macro.maven == the radical programmer (talk) 04:30, 16 October 2015 (CDT)