topic Re: How to reshape data in SAS with summarization (sum/count) in SAS Programming

How to reshape data in SAS with summarization (sum/count)

captainprice0 — Wed, 06 May 2020 17:16:14 GMT

I have a table that looks like this,

date1	var1
date1	var2
date2	var2
date2	var3
date3	var3
date3	var3

and I want it to look like this,

	var1	var2	var3
date1	1	1	0
date2	0	1	1
date3	0	0	2

I managed to write a transpose but got a duplicate error here.
it is super easy to do in R using reshape package, but how would this be done in SAS here?

Re: How to reshape data in SAS with summarization (sum/count)

novinosrin — Wed, 06 May 2020 17:27:35 GMT

data have;
input date $ var $;
cards;
date1	var1
date1	var2
date2	var2
date2	var3
date3	var3
date3	var3
;

proc freq data=have noprint;
tables date*var/sparse out=temp(drop=percent);
run;
proc transpose data=temp out=want(drop=_:);
by date;
id var;
var count;
run;

Re: How to reshape data in SAS with summarization (sum/count)

Reeza — Wed, 06 May 2020 19:42:20 GMT

Do you want a table/data set or a displayed report? If you need a report, proc tabulate or report are the right procedures.
Transpose won't summarize and transpose at the same time so you need to do those separately.
Proc means would also generate that table if desired.

Re: How to reshape data in SAS with summarization (sum/count)

Tom — Wed, 06 May 2020 20:05:20 GMT

Hard to tell what you data actually looks like when you only show listings.

Let's assume you have two variables named DATE and VAR in a dataset named HAVE.

Hard to tell what you want when you only show a listing. So let's assume you just want a report.

data have;
  input date $ var $;
cards;
date1	var1
date1	var2
date2	var2
date2	var3
date3	var3
date3	var3
;

options missing='0';
proc report data=have ;
  column date n,var ;
  define date/group ' ';
  define n / ' ';
  define var / across ' ';
run;
options missing='.';

                 var1       var2       var3
  date1             1          1          0
  date2             0          1          1
  date3             0          0          2

If you actually want a dataset then you probably don't want it in that useless structure. You can use the SPARSE option in PROC FREQ to insert the zeros for the missing combinations.

proc freq data=have ;
  tables date*var / noprint out=tall sparse;
run;

proc print data=tall;
run;

Obs    date     var     COUNT    PERCENT

 1     date1    var1      1      16.6667
 2     date1    var2      1      16.6667
 3     date1    var3      0       0.0000
 4     date2    var1      0       0.0000
 5     date2    var2      1      16.6667
 6     date2    var3      1      16.6667
 7     date3    var1      0       0.0000
 8     date3    var2      0       0.0000
 9     date3    var3      2      33.3333

If you did want to convert it to a dataset in that hard to use structure that is implied by your original listing then PROC TRANSPOSE can do it, assuming that the values of VAR are actually valid variable names.

proc transpose data=tall out=wide(drop=_name_ _label_);
  by date;
  id var;
  var count;
run;

Obs    date     var1    var2    var3

 1     date1      1       1       0
 2     date2      0       1       1
 3     date3      0       0       2

Re: How to reshape data in SAS with summarization (sum/count)

Ksharp — Thu, 07 May 2020 11:30:32 GMT

one more way:

data have;
  input date $ var $;
cards;
date1	var1
date1	var2
date2	var2
date2	var3
date3	var3
date3	var3
;
proc tabulate data=have format=f12.0;
class date var;
table date=' ',var=' '*n=' '/misstext='0';
run;