## average

Solved
Regular Contributor
Posts: 171

# average

Hi All,

i have some records with multiple data. i am looking for average for those records, i have numeric and character variables

This is the sample , i kept only few records.i have more than 100 variables

exampl

Data have;

Input sub  \$ cat  \$  val \$    val2(numeric)

Cards;

100        one            2.1         2.1

100        one            4.2         4.2

100        two            3.1        3.1

100        two            3.4         3.4

101        one            7.8         9

101        two            3.2         8

want:

100        one            3.15            3.15

100        two             3.25           3.25

101       one            7.8               9

101     two             3.2              8

Thanks

Sam

Accepted Solutions
Solution
‎12-18-2014 04:36 PM
PROC Star
Posts: 8,164

## Re: average

Or, if you have a lot of variables, let proc sql do the typing for you.  e.g.,:

data have;

input sub  \$ cat  \$  val \$    val2;

cards;

100        one            2.1         2.1

100        one            4.2         4.2

100        two            3.1        3.1

100        two            3.4         3.4

101        one            7.8         9

101        two            3.2         8

;

proc sql;

select distinct

case type

when 'char' then catx(' ','mean(input(',name,',12.)) as',name)

else catx(' ','mean(',name,') as',name)

end

into :vars

separated by ','

from dictionary.columns

where libname='WORK' and

memname='HAVE' and

name not in ('sub','cat')

order by varnum

;

create table want as

select sub,cat,&vars.

from have

group by sub,cat

;

quit;

All Replies
Posts: 1,270

## Re: average

proc sql;

create table want as

select sub,cat,mean(input(val,4.1)) as val,mean(val2) as val2

from have

group by sub,cat;

quit;

Solution
‎12-18-2014 04:36 PM
PROC Star
Posts: 8,164

## Re: average

Or, if you have a lot of variables, let proc sql do the typing for you.  e.g.,:

data have;

input sub  \$ cat  \$  val \$    val2;

cards;

100        one            2.1         2.1

100        one            4.2         4.2

100        two            3.1        3.1

100        two            3.4         3.4

101        one            7.8         9

101        two            3.2         8

;

proc sql;

select distinct

case type

when 'char' then catx(' ','mean(input(',name,',12.)) as',name)

else catx(' ','mean(',name,') as',name)

end

into :vars

separated by ','

from dictionary.columns

where libname='WORK' and

memname='HAVE' and

name not in ('sub','cat')

order by varnum

;

create table want as

select sub,cat,&vars.

from have

group by sub,cat

;

quit;

🔒 This topic is solved and locked.

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

Discussion stats
• 2 replies
• 228 views
• 3 likes
• 3 in conversation