topic Re: how to fix data in SAS Programming

how to fix data

thanikondharish — Wed, 15 Aug 2018 10:59:39 GMT

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

Re: how to fix data

RW9 — Wed, 15 Aug 2018 10:59:15 GMT

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;

Re: how to fix data

thanikondharish — Wed, 15 Aug 2018 11:01:22 GMT

sorry that data should be made by proc report

Re: how to fix data

RW9 — Wed, 15 Aug 2018 11:05:42 GMT

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.

Re: how to fix data

thanikondharish — Wed, 15 Aug 2018 11:50:22 GMT

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

Re: how to fix data

RW9 — Wed, 15 Aug 2018 12:27:10 GMT

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.

Re: how to fix data

Cynthia_sas — Wed, 15 Aug 2018 12:49:15 GMT

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

Re: how to fix data

Reeza — Wed, 15 Aug 2018 14:03:04 GMT

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

Re: how to fix data

ballardw — Wed, 15 Aug 2018 14:49:27 GMT

@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;