SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

sasprogramming_0-1621841728243.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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;
Ksharp
Super User
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;

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2505 views
  • 1 like
  • 3 in conversation