Help using Base SAS procedures

grouping a sorted data set

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

grouping a sorted data set

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.

IdDateDokumentsMonth
B01654029702. Jan 1315.149Jan 13
B01655247803. Jan 1311.799Jan 13
B01655414404. Jan 139.478Jan 13
B01656890905. Jan 136.828Jan 13
B01657306706. Jan 134.097Jan 13
B01657776107. Jan 139.728Jan 13
B01659509808. Jan 1311.816Jan 13
B01660727509. Jan 1311.988Jan 13
B01662127010. Jan 1313.756Jan 13
B01663009311. Jan 1311.560Jan 13
B01663644512. Jan 133.435Jan 13
B01663646913. Jan 1325Jan 13
B01665379714. Jan 1317.127Jan 13
B01665695315. Jan 1313.365Jan 13
B01668154116. Jan 1313.885Jan 13
B01670524917. Jan 1323.493Jan 13
B01670721818. Jan 1311.365Jan 13
B01672092019. Jan 134.192Jan 13
B01672305420. Jan 132.158Jan 13
B01673233721. Jan 1310.489Jan 13
B01674690022. Jan 1312.749Jan 13
B01676005323. Jan 1312.979Jan 13
B01677203824. Jan 1311.710Jan 13
B01678328925. Jan 1311.095Jan 13
B01678450626. Jan 133.826Jan 13
B01679116927. Jan 134.134Jan 13
B01684343828. Jan 1351.937Jan 13
B01685549329. Jan 1311.782Jan 13
B01686146930. Jan 1312.486Jan 13
B01687879631. Jan 1310.346Jan 13
B02172393801. Jan 1451Jan 14
B02174584602. Jan 1421.674Jan 14
B02175564403. Jan 149.570Jan 14
B02175716604. Jan 149.977Jan 14
B02176615605. Jan 14367Jan 14
B02177471106. Jan 148.271Jan 14
B02178295707. Jan 1411.648Jan 14
B02179878508. Jan 1412.010Jan 14
B02180932009. Jan 1410.469Jan 14
B02182480310. Jan 1415.154Jan 14
B02182511411. Jan 14371Jan 14
B02182520012. Jan 143Jan 14
B02183545813. Jan 1419.406Jan 14
B02186027714. Jan 1425.229Jan 14
B02188437115. Jan 1412.738Jan 14
B02189488116. Jan 1410.200Jan 14
B02191280317. Jan 1429.037Jan 14
B02192979718. Jan 145.358Jan 14
B02193091319. Jan 141.215Jan 14

Accepted Solutions
Solution
‎06-12-2015 11:49 PM
Respected Advisor
Posts: 4,925

Re: grouping a sorted data set

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

PG

View solution in original post


All Replies
Super User
Posts: 5,430

Re: grouping a sorted data set

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

Data never sleeps
Super User
Posts: 11,343

Re: grouping a sorted data set

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.

Super User
Posts: 19,817

Re: grouping a sorted data set

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.

Occasional Contributor
Posts: 6

Re: grouping a sorted data set

i would like add a new columns of course

Solution
‎06-12-2015 11:49 PM
Respected Advisor
Posts: 4,925

Re: grouping a sorted data set

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

PG
Occasional Contributor
Posts: 6

Re: grouping a sorted data set

Thx, that worked!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 339 views
  • 0 likes
  • 5 in conversation