summarized report creation using - proc tabulate or ?

Reply
Occasional Contributor
Posts: 19

summarized report creation using - proc tabulate or ?

I've the below dataset as input

ID category cust.nr cust.name income

1 a 100 Crosbie 5000
2 a 200 Heier 5500
2 a 300 Pick 5500
3 a 400 Sandridge 5100
4 b 500 Groesbeck 10000
4 b 600 Hayton 11000
4 b 700 Razor 12000
5 c 800 Lamere 90000

I need a report (f.ex using proc tabulate) as follows

In the data, cust.nr are unique but all the customers belonging to one family are given same ID, and customers are categorized based on their income.

<10000 as a
10000 to 15000 as b
>15000 as c

I need a report with

count of unique IDs(families), grouped by categories, and also rest of the columns need to be shown in the report.

so, it should look like

count_ID categorycust.nr cust.name   income
100  Crosbie 5000
200Heier5500
3a300Pick 5500
400  Sandridge    5100
500 Groesbeck 10000
1b600 Hayton11000
700 Razor 12000
1c800 Lamere 90000

Any suggestions please..

Super User
Posts: 17,868

Re: summarized report creation using - proc tabulate or ?

If you need a count of distinct ID's you'll need to prepare the data ahead of time and then display with your choice of proc's, proc report or print is my suggestion.

Occasional Contributor
Posts: 19

Re: summarized report creation using - proc tabulate or ?

I don't think we can generate the above report or (atleast I don't know), would you be kind to help using the above data.

Super User
Posts: 17,868

Re: summarized report creation using - proc tabulate or ?

Here's how to generate the data. The report style and such can be done in Proc Report.

data have;

input id category $ cust name $ income;

cards;

1 a 100 Crosbie 5000

2 a 200 Heier 5500

2 a 300 Pick 5500

3 a 400 Sandridge 5100

4 b 500 Groesbeck 10000

4 b 600 Hayton 11000

4 b 700 Razor 12000

5 c 800 Lamere 90000

;

run;

proc sql;

    create table want as

    select count(distinct id) as count_id, category, cust, name, income

    from have

    group by category

    order by category, cust;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 192 views
  • 3 likes
  • 2 in conversation