topic pivot table equivalent in sas in SAS Programming

pivot table equivalent in sas

sasprogramming — Mon, 24 May 2021 07:36:01 GMT

I have to following dataset that I want to summarise in SAS. Attached below is my desired data set. In excel I would make a pivot table, however I am not sure what commands I would use in sas to reach my desired data. I am new to SAS.

Thanks

ID	Date	Amount
38134805	1/04/2017	53
38257051	1/04/2017	54
38263562	1/04/2017	91
38335667	1/04/2017	76
38368668	1/04/2017	111
38373205	1/04/2017	103
38375879	1/04/2017	50
38385977	1/04/2017	85
38134805	1/05/2017	117
38257051	1/05/2017	91
38263562	1/05/2017	59
38335667	1/05/2017	107
38368668	1/05/2017	85
38373205	1/05/2017	87
38375879	1/05/2017	118
38385977	1/05/2017	62
38134805	1/06/2017	115
38257051	1/06/2017	52
38263562	1/06/2017	58
38335667	1/06/2017	61
38368668	1/06/2017	102
38373205	1/06/2017	59
38375879	1/06/2017	113
38385977	1/06/2017	51

Re: pivot table equivalent in sas

Kurt_Bremser — Mon, 24 May 2021 07:42:09 GMT

Try this:

proc report data=have;
column id n,date;
define id /group;
define date / "" across format=yymmd7.;
define n / "";
run;

Completely untested, posted from my tablet.

Re: pivot table equivalent in sas

Kurt_Bremser — Mon, 24 May 2021 10:45:48 GMT

And this (tested now) creates the "Total" column:

proc report data=have;
column id date,n ("Total" n=total);
define id / group;
define date / "" across format=yymmd7.;
define n / "";
define total /"";
run;

Re: pivot table equivalent in sas

Ksharp — Mon, 24 May 2021 12:20:38 GMT

data have;
infile cards truncover expandtabs;
input ID	Date : ddmmyy10.	Amount;
cards;
38134805	1/04/2017	53
38257051	1/04/2017	54
38263562	1/04/2017	91
38335667	1/04/2017	76
38368668	1/04/2017	111
38373205	1/04/2017	103
38375879	1/04/2017	50
38385977	1/04/2017	85
38134805	1/05/2017	117
38257051	1/05/2017	91
38263562	1/05/2017	59
38335667	1/05/2017	107
38368668	1/05/2017	85
38373205	1/05/2017	87
38375879	1/05/2017	118
38385977	1/05/2017	62
38134805	1/06/2017	115
38257051	1/06/2017	52
38263562	1/06/2017	58
38335667	1/06/2017	61
38368668	1/06/2017	102
38373205	1/06/2017	59
38375879	1/06/2017	113
38385977	1/06/2017	51
;
proc tabulate data=have;
class id date;
format date monname3.;
table id=' ' all,date=' ' all/box='ID';
keylabel all='Grand Total' n=' ';
run;