BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

2 REPLIES 2
stat_sas
Ammonite | Level 13

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;

art297
Opal | Level 21

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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