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

Hi All,

I have a lot of variables in my dataset.

variables in my dataset:

id w1 w2 w3 h1 h2 h3 age1 age2 ag3 .... plus a lot other variables ...rate1 rate2 rat3.

I need to calculate means of:

mean_w=mean(of,w1-w3);

mean_h=mean(of,h1-h3);

...

mean_rate=mean(of,rate1-rate3);

what is the best way to do it?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Slight modification of my previous suggestion to account for variable ranges that go beyond 1 digit.  I think this is a lot more direct than dealing with arrays in this case as the functions can deal directly with a variable prefix:

data have;

  input id $ this1-this3 that1-that3 x1-x10 y;

  cards;

1 1 2 1 4 3 2 5 6 7 1 2 3 4 5 6 7 9

2 5 6 7 3 2 4 8 6 7 1 2 3 4 5 6 7 9

;

proc sql noprint;

    select "mean_"||

           strip(name)||

           "=mean(of "||

           strip(name)||

           ":);"

      into :getmeans separated by " "

        from (select distinct substr(name,1,length(name)-

          anyalpha(reverse(strip(name)))+1) as name

            from dictionary.columns

             where libname="WORK" and

                memname="HAVE" and

                type="num" and

                anydigit(substr(name,length(name),1))

                  )

  ;

quit;

data want;

  set have;

  &getmeans.;

run;

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

Other than restructuring that data so that the repetitions are rows?

How about arrays?

data have;

  input id w1-w3 h1-h3 ;

cards;

1 1 2 3 10 11 12

2 4 5 6 13 14 15

run;

data want;

  set have;

  array reps (2,3) w1--h3;

  array means mean_w mean_h ;

  do i=1 to dim(means);

    means(i) = mean(of reps(i,1),reps(i,2),reps(i,3));

  end;

run;

Linlin
Lapis Lazuli | Level 10

Hi Tom,

I like your array solution.

Since the OP stated there are a lot of variables, putting the variables into macro variables may save some typing:

data have;
  input id w1-w3 h1-h3 ag1-ag3 rate1-rate3;
cards;
1 1 2 3 10 11 12 20 21 23 90 91 92
2 4 5 6 13 14 15 20 21 23 90 . 92
;
run;
proc contents data=have out=vars(keep=name varnum ) noprint; run;
proc sql noprint ;
           select cats('mean_',substr(name,1,length(name)-1)) into : names separated by ' '
              from vars
                 where upcase(name) ne 'ID' and substr(name,length(name))='3'
                     order by varnum;
     select name into : allvar separated by ' '
         from vars
            where substr(name,length(name)) in ('1','2','3')
                order by varnum;
quit;

%let n=%sysfunc(countw(&names));

data want;
  set have;
  array reps (&n,3) &allvar;
  array means &names ;
  do i=1 to dim(means);
    means(i) = mean(of reps(i,1),reps(i,2),reps(i,3));
  end;
  drop i;
run;
proc print;run;

art297
Opal | Level 21

If none of your variable ranges go beyond a single digit you might be able to do it with the following.  However, you'd have to adjust the code if any go beyond a single digit:

data have;

  input id $ this1-this3 that1-that3 x1-x3 y;

  cards;

1 1 2 1 4 3 2 5 6 7 9

2 5 6 7 3 2 4 8 6 7 9

;

proc sql noprint;

    select "mean_"||

           strip(name)||

           "=mean(of "||

           strip(name)||

           ":);"

      into :getmeans separated by " "

        from (select distinct substr(name,1,length(name)-1) as name

            from dictionary.columns

             where libname="WORK" and

                memname="HAVE" and

                type="num" and

                anydigit(substr(name,length(name),1))

                  )

  ;

quit;

data want;

  set have;

  &getmeans.;

run;

art297
Opal | Level 21

Slight modification of my previous suggestion to account for variable ranges that go beyond 1 digit.  I think this is a lot more direct than dealing with arrays in this case as the functions can deal directly with a variable prefix:

data have;

  input id $ this1-this3 that1-that3 x1-x10 y;

  cards;

1 1 2 1 4 3 2 5 6 7 1 2 3 4 5 6 7 9

2 5 6 7 3 2 4 8 6 7 1 2 3 4 5 6 7 9

;

proc sql noprint;

    select "mean_"||

           strip(name)||

           "=mean(of "||

           strip(name)||

           ":);"

      into :getmeans separated by " "

        from (select distinct substr(name,1,length(name)-

          anyalpha(reverse(strip(name)))+1) as name

            from dictionary.columns

             where libname="WORK" and

                memname="HAVE" and

                type="num" and

                anydigit(substr(name,length(name),1))

                  )

  ;

quit;

data want;

  set have;

  &getmeans.;

run;

Linlin
Lapis Lazuli | Level 10

Hi Art,

If I want to see the value of your macro variable &getmeans and add "%put &getmeans;" to your code, is there a way to avoid the error messages in log file?

Thank you!

art297
Opal | Level 21

: How about;

options symbolgen;

title "&getmeans." ;

Linlin
Lapis Lazuli | Level 10

Thank you Art! - Linlin

HG
Calcite | Level 5 HG
Calcite | Level 5

All worked great.  Thank you very much for your help!!!Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1162 views
  • 10 likes
  • 4 in conversation