grouping a sorted data set

[Landry]

consider the following data set.

how do i create a report from it with the data steop by grouping all documents per month in a new column as well as

all documents per year in another column, as it is done for the documents per day?

thx a lot.

Id	Date	Dokuments	Month
B016540297	02. Jan 13	15.149	Jan 13
B016552478	03. Jan 13	11.799	Jan 13
B016554144	04. Jan 13	9.478	Jan 13
B016568909	05. Jan 13	6.828	Jan 13
B016573067	06. Jan 13	4.097	Jan 13
B016577761	07. Jan 13	9.728	Jan 13
B016595098	08. Jan 13	11.816	Jan 13
B016607275	09. Jan 13	11.988	Jan 13
B016621270	10. Jan 13	13.756	Jan 13
B016630093	11. Jan 13	11.560	Jan 13
B016636445	12. Jan 13	3.435	Jan 13
B016636469	13. Jan 13	25	Jan 13
B016653797	14. Jan 13	17.127	Jan 13
B016656953	15. Jan 13	13.365	Jan 13
B016681541	16. Jan 13	13.885	Jan 13
B016705249	17. Jan 13	23.493	Jan 13
B016707218	18. Jan 13	11.365	Jan 13
B016720920	19. Jan 13	4.192	Jan 13
B016723054	20. Jan 13	2.158	Jan 13
B016732337	21. Jan 13	10.489	Jan 13
B016746900	22. Jan 13	12.749	Jan 13
B016760053	23. Jan 13	12.979	Jan 13
B016772038	24. Jan 13	11.710	Jan 13
B016783289	25. Jan 13	11.095	Jan 13
B016784506	26. Jan 13	3.826	Jan 13
B016791169	27. Jan 13	4.134	Jan 13
B016843438	28. Jan 13	51.937	Jan 13
B016855493	29. Jan 13	11.782	Jan 13
B016861469	30. Jan 13	12.486	Jan 13
B016878796	31. Jan 13	10.346	Jan 13
B021723938	01. Jan 14	51	Jan 14
B021745846	02. Jan 14	21.674	Jan 14
B021755644	03. Jan 14	9.570	Jan 14
B021757166	04. Jan 14	9.977	Jan 14
B021766156	05. Jan 14	367	Jan 14
B021774711	06. Jan 14	8.271	Jan 14
B021782957	07. Jan 14	11.648	Jan 14
B021798785	08. Jan 14	12.010	Jan 14
B021809320	09. Jan 14	10.469	Jan 14
B021824803	10. Jan 14	15.154	Jan 14
B021825114	11. Jan 14	371	Jan 14
B021825200	12. Jan 14	3	Jan 14
B021835458	13. Jan 14	19.406	Jan 14
B021860277	14. Jan 14	25.229	Jan 14
B021884371	15. Jan 14	12.738	Jan 14
B021894881	16. Jan 14	10.200	Jan 14
B021912803	17. Jan 14	29.037	Jan 14
B021929797	18. Jan 14	5.358	Jan 14
B021930913	19. Jan 14	1.215	Jan 14

[LinusH]

Consider leaving a sample output. Will probably give you better answers.

[ballardw]

Dealing with dates and grouping the first step would be to ensure that the DATE variable is a SAS date valued variable.

It appears that your are requiring a datastep solution. Is that correct? If so I always wonder why when depending on the types of summarization Proc Means/ Summary is the ideal solution.

[Reeza]

It's not clear what you mean by:

grouping all documents per month in a new column as well as
all documents per year in another column

Do you want to add a column to the current dataset or create two new datasets or something else?

If you're adding columns I like SQL solutions.

[Landry]

i would like add a new columns of course

[PGStats · Accepted solution]

My guess is that you want something like this...

data have;

input Id :$10. dateStr &:$10. Dokuments :commax.;

date = input(compress(dateStr,". "), date7.);

format date ddmmyy10.;

drop dateStr;

datalines;

B016540297  02. Jan 13  15.149

B016552478  03. Jan 13  11.799

B016554144  04. Jan 13  9.478

B016568909  05. Jan 13  6.828

B016573067  06. Jan 13  4.097

B016577761  07. Jan 13  9.728

B016595098  08. Jan 13  11.816

B016607275  09. Jan 13  11.988

B016621270  10. Jan 13  13.756

B016630093  11. Jan 13  11.560

B016636445  12. Jan 13  3.435

B016636469  13. Jan 13  25

B016653797  14. Jan 13  17.127

B016656953  15. Jan 13  13.365

B016681541  16. Jan 13  13.885

B016705249  17. Jan 13  23.493

B016707218  18. Jan 13  11.365

B016720920  19. Jan 13  4.192

B016723054  20. Jan 13  2.158

B016732337  21. Jan 13  10.489

B016746900  22. Jan 13  12.749

B016760053  23. Jan 13  12.979

B016772038  24. Jan 13  11.710

B016783289  25. Jan 13  11.095

B016784506  26. Jan 13  3.826

B016791169  27. Jan 13  4.134

B016843438  28. Jan 13  51.937

B016855493  29. Jan 13  11.782

B016861469  30. Jan 13  12.486

B016878796  31. Jan 13  10.346

B021723938  01. Jan 14  51

B021745846  02. Jan 14  21.674

B021755644  03. Jan 14  9.570

B021757166  04. Jan 14  9.977

B021766156  05. Jan 14  367

B021774711  06. Jan 14  8.271

B021782957  07. Jan 14  11.648

B021798785  08. Jan 14  12.010

B021809320  09. Jan 14  10.469

B021824803  10. Jan 14  15.154

B021825114  11. Jan 14  371

B021825200  12. Jan 14  3

B021835458  13. Jan 14  19.406

B021860277  14. Jan 14  25.229

B021884371  15. Jan 14  12.738

B021894881  16. Jan 14  10.200

B021912803  17. Jan 14  29.037

B021929797  18. Jan 14  5.358

B021930913  19. Jan 14  1.215

;

proc sql;

create table want as

select *, sum(Dokuments) as yearDoks

from (

    select *, sum(Dokuments) as monthDoks

    from have

    group by intnx("MONTH",date,0))

group by intnx("YEAR",date,0);

select * from want;

quit;

PG

[Landry]

Thx, that worked!