Help using Base SAS procedures

average

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

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: 7,363

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

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: 7,363

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.

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

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