How to do grouping column wise in SAS similar to pivot table

Question

I am trying to group data in the column in SAS like how you can do in pivot table in excel. I'm trying the produce the following desired output.

Problem 11/17/2019-11/23/2019 INC 25 please see the image SA 15 VV 10

I have tried PROC SQL but not sure how to group in column wise like those dates. Let me know if you need additional info on this, also have attached the image

Answer 1

Some options for creating output that displays frequency counts of categorical data in tabular form and pivoting data itself:

• Proc TABULATE
• Proc REPORT
• Proc FREQUENCY
• Proc TRANSPOSE - do you really want data (weeks) as metadata (column names)
• SQL (arduous)

Suppose your data has columns visitId, date, and SS:

data visits;
  call streaminit(1234);

  do date = '01jan2019'd to '31dec2019'd;
    do _n_ = 1 to 5 + rand('uniform', 11);  /* between 5 and 15 ss codes a day */
      visitId + 1;
      length ss $5;
      ss = scan ("CS,FALL,ELBOW,ANKLE,LS,PS,SA,VV",ceil(rand('uniform',8)));
      output;
    end;
  end;

  format date yymmdd10.;
run;

Compute a new variable containing the week of the visit. This variable is used as a bucket for aggregate grouping.

data have;
  set visits;
  weekof = intnx('week', date, 0);          * compute bucket value for aggregation over weeks;
  attrib weekof format=mmddyy10. label='Week of';
run;

Use a procedure to generate output:

proc tabulate data=have;
  title 'Tabulate - weeks are columns';
  where year(weekof) = 2019 and month(weekof) = 11;    * restrict to a single month;
  class ss weekof;
  table ss='', weekof * n='';     * column dimension is weekof (one column per weekof value);
run;

proc tabulate data=have;
  title 'Tabulate - weeks are rows';
  where year(weekof) = 2019 and qtr(weekof) = 4;
  class ss weekof;
  table weekof, ss=''*n='' / nocellmerge;     * row dimension is weekof (one row per weekof value);
  table weekof='', ss=''*n='' / box='Week of';          * row dimension is weekof (one row per weekof value);
run;

proc report data=have split='A0'x;
  title 'Report - weeks are columns';
  where year(weekof) = 2019 and month(weekof) = 11;    * restrict to a single month;
  column ss weekof;
  define weekof / across;
  define ss / group; 
run;

proc freq data=have;
  title 'Freq - weeks are columns';
  where year(weekof) = 2019 and month(weekof) = 11;    * restrict to a single month;
  table ss * weekof / norow nocol nocum nopercent;
run;

TRANSPOSE

Compute counts over SS and week, transpose that

proc sql;
  create table have_counts as 
  select ss, weekof, count(*) as freq
  from have
  group by ss, weekof
  order by ss, weekof
  ;
proc transpose data=have_counts out=have_across_week(drop=_name_);
  where year(weekof) = 2019 and month(weekof) = 11;    * restrict to a single month;
  by ss;
  id weekof;
  var freq;
run;

SQL

SQL code for pivoting is tedious and error prone during coding. It is also not automatically extensible when new dates come into the data. Having lots of similar statements (those SUMs) is known as wallpaper code, and who likes hanging wallpaper ?

proc sql;
  create table ss_freq_across_weeks as
  select 
    ss
    , sum ( intnx('week', date, 0) = '03-NOV-2019'D ) as week1 label = 'Week of 11/03/2019'
    , sum ( intnx('week', date, 0) = '10-NOV-2019'D ) as week2 label = 'Week of 11/10/2019'
    , sum ( intnx('week', date, 0) = '17-NOV-2019'D ) as week3 label = 'Week of 11/17/2019'
    /*...*/
  from have
  group by ss
  ;