how to fix data

[thanikondharish]

proc freq data=sashelp.class noprint ;
table sex*age/out=freq(drop=percent) ;
run;

if i run above program it gives like see below 

Sex	Age	Frequency Count
F	11	1
F	12	2
F	13	2
F	14	2
F	15	2
M	11	1
M	12	3
M	13	1
M	14	2
M	15	2
M	16	1

but i want to data like see below (use proc report)

type	total
M	10
11	1
12	3
13	1
14	2
15	2
16	1
F	9
11	1
12	2
13	2
14	2
15	2

[RW9]

So you process the results into what you want the output to look like?  Maybe something like:

proc sql;
  create table want as 
  select  sex as type,
          sum(frequency_count) as total,
          1 as ord
  from    freq
  group by sex
  union all
  select  strip(put(age.best.)),
          frequency_count,
          2 as ord

  from    freq
  order by type,ord;
quit;

[thanikondharish]

sorry that data should be made by proc report

[RW9]

Why?  Is that the only procedure you have?  Use the tools best suited to the task at hand.  Data processing is best done by datastep (or procedures), reporting out data is done by reporting procedures.

[thanikondharish]

proc sql ;create table ex as
select sex,count(age) as count,age from sashelp.class group by sex,age
order by sex;
quit;

if i run above program it gives data like see below

*Sex*

*count*

*Age*

F

1

11

F

2

12

F

2

13

F

2

14

F

2

15

M

1

11

M

3

12

M

1

13

M

2

14

M

2

15

M

1

16

but i want to fix dataset like see below

*type*

*total*

M

10

11

1

12

3

13

1

14

2

15

2

16

1

F

9

11

1

12

2

13

2

14

2

15

2

[RW9]

And how does that relate to the code I provided you?  Please re-read my original post, the first part of the SQL counts the overall grouping - i.e. by sex, the second adds the counts generated by the proc freq you posted.  Then they just get sorted.

[Cynthia_sas]

Hi:

  PROC REPORT will not "mix" sex and age into one column, but you can run code like this:

proc report data=sashelp.class;
  column sex age n;
  define sex / group  ;
  define age / 'type' group;
  define n / 'total';
  break before sex / summarize;
  compute after sex;
     line ' ';
  endcomp;
run;

which comes very close to the report the OP was aiming for.

Cynthia

[ballardw]

---

@thanikondharish wrote:
sorry that data should be made by proc report

---

You subject is off. You are not fixing any data. You are displaying data. 

Since you are showing a character and numeric variable in the same column of a report table SAS generally will NOT do that with a simple proc call.

I can make something like that but it takes a couple of steps usually involving one or more procedures to summarize the data and then a data step massaging output to restructure things, such as creating a character version of the age variable, so that the values may appear in a single column.

One way that gets close

proc tabulate data=sashelp.class;
   class sex /descending;
   class age;
   table sex=''*(all='Total' age=' '),
         n
   ;
run;

[Reeza]

PROC TABULATE will do this in about 4 lines of code.

---

@thanikondharish wrote:

proc freq data=sashelp.class noprint ;
table sex*age/out=freq(drop=percent) ;
run;

if i run above program it gives like see below 

Sex	Age	Frequency Count
F	11	1
F	12	2
F	13	2
F	14	2
F	15	2
M	11	1
M	12	3
M	13	1
M	14	2
M	15	2
M	16	1

but i want to data like see below (use proc report)

type	total
M	10
11	1
12	3
13	1
14	2
15	2
16	1
F	9
11	1
12	2
13	2
14	2
15	2

---