topic Re: grouping a sorted data set in SAS Procedures

grouping a sorted data set

Landry — Fri, 12 Jun 2015 07:19:18 GMT

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

Re: grouping a sorted data set

LinusH — Fri, 12 Jun 2015 07:29:32 GMT

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

Re: grouping a sorted data set

ballardw — Fri, 12 Jun 2015 15:48:52 GMT

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.

Re: grouping a sorted data set

Reeza — Fri, 12 Jun 2015 17:03:55 GMT

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.

Re: grouping a sorted data set

PGStats — Sat, 13 Jun 2015 03:49:48 GMT

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;

Re: grouping a sorted data set

Landry — Mon, 15 Jun 2015 06:23:39 GMT

i would like add a new columns of course

Re: grouping a sorted data set

Landry — Mon, 15 Jun 2015 07:54:36 GMT

Thx, that worked!