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

Dear SAS Users,

I have data as follows:

date corr1_2 corr1_3..... corr19_20

for each date, the variable corr1_2  shows the correlation between item1 and item2, ..., corr19_20 shows the correlation between item19 and item20. I have all combinations of pairwise correlations for different dates.

I have macro variables that holds 18,19 and 20 (end numbers). I calculated mean correlation for each day by using a line as follows:

meancorr=mean( %do i=1 %to &lastbmax2; %do j=&i+1 %to &max; corr&i._&j, %end;  %end; corr&lastbmax1._&max);

where lastbmax2, lastbmax1 and max are 18, 19, and 20, respectively.

My question is that how can I calculate the mean of the bivariate correlations which are lower than the 5th and higher than the 95th percentiles for each day? I can identify the percentiles by using the pctl function. But how can I calculate the means as I just described?

Thanks for any help in advance.

J.

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

This solution uses the array suggested by ArtT, but loops through the array rather than sorting the array, which would be more elegant.

data have;
input date date9. corr1_2 corr1_3 corr2_3;
datalines;
16oct2012 .1 .9 .2
17oct2012 .5 .7 .3
run;

data want;*(keep=date meancorr umean lmean);
   set have;
   array allcorr {*} corr:;
   meancorr = mean(of corr:);
   lower=pctl(5,of corr:);
   upper=pctl(95,of corr:);
   
   lmean=0; lcnt=0; umean=0; ucnt=0;
   do i = 1 to dim(allcorr);
      if allcorr{i} le lower then do;
         lmean + allcorr{i};
         lcnt + 1;
      end;
      if allcorr{i} ge upper then do;
         umean + allcorr{i};
         ucnt + 1;
      end;
    end;
    lmean=lmean/lcnt;
    umean=umean/ucnt;
    output want;
   run;
proc print data=want;
run;

View solution in original post

2 REPLIES 2
art297
Opal | Level 21

I think you are complicating the task by including all of the correlations in macro variables.  If you simply loaded then into an array, in a datastep, you might be able to easily accomplish both tasks by using call sortn to sort the array.  Then, you would only have to use the of operator to include all of the correlations in the overall mean calculation and, for the top and bottom percentiles (unless I misunderstand your data), simply include the x percent at the top and bottom of the sorted array.

ArtC
Rhodochrosite | Level 12

This solution uses the array suggested by ArtT, but loops through the array rather than sorting the array, which would be more elegant.

data have;
input date date9. corr1_2 corr1_3 corr2_3;
datalines;
16oct2012 .1 .9 .2
17oct2012 .5 .7 .3
run;

data want;*(keep=date meancorr umean lmean);
   set have;
   array allcorr {*} corr:;
   meancorr = mean(of corr:);
   lower=pctl(5,of corr:);
   upper=pctl(95,of corr:);
   
   lmean=0; lcnt=0; umean=0; ucnt=0;
   do i = 1 to dim(allcorr);
      if allcorr{i} le lower then do;
         lmean + allcorr{i};
         lcnt + 1;
      end;
      if allcorr{i} ge upper then do;
         umean + allcorr{i};
         ucnt + 1;
      end;
    end;
    lmean=lmean/lcnt;
    umean=umean/ucnt;
    output want;
   run;
proc print data=want;
run;

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
  • 2 replies
  • 865 views
  • 0 likes
  • 3 in conversation