BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8
Period123456
.....................
20140122223303034000080000123444144000
2014022233440002100202120000132000.
201403129934150000123444130000..
2014045434975000123444...
20150199900134423....
201502102030.....

I have created triangles of data in SAS. When I say triangles, the cells off the triangle are left blank. The actual triangle is much bigger than this one and I'm trying to write the code to sum over the bottom nonblank cells of each column. I'm not sure if this is possible but I would think it would involve proc summary.

Are there any functions that can achieve this?

Thank you  

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

You could use arrays:

data want;

     set have;

     array var{6};  /* I assume your variables are prefixed with var */

     do i=1 to 6;

          if var{i}=. then do;

               do j=i-1 to 1 by -1;

                    result=sum(result,var{j});

               end;

               leave;

          end;

     end;

run;

So the above looks for the last record (i.e. the first .), then takes the previous numbers and sums them up.  You could do mean of certain, e.g. change the to 1 to 3, and mean is just sum / i-1.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

You could use arrays:

data want;

     set have;

     array var{6};  /* I assume your variables are prefixed with var */

     do i=1 to 6;

          if var{i}=. then do;

               do j=i-1 to 1 by -1;

                    result=sum(result,var{j});

               end;

               leave;

          end;

     end;

run;

So the above looks for the last record (i.e. the first .), then takes the previous numbers and sums them up.  You could do mean of certain, e.g. change the to 1 to 3, and mean is just sum / i-1.

brophymj
Quartz | Level 8

Thanks RW9

That works when summing the last 6 cells on a row but I wanted to get the average of the last 8 nonzero cells on each column. The length of the triangle is 36*36 so I want to take the average of the last 8.

Using the above example (and assuming I only want the average of the last 2 non-zero cells I would want

(102030+99900)/2 = 100965

(134423+75000)/2 = 104711.5

(123444+123444)/2 = 123444

(130000+120000)/2 = 125000

(132000+123444)/2 = 127722

144000 (where there are less than the number we want to get to average over it just gets the average of what's there|). 

data_null__
Jade | Level 19

Seems like a job for STAT functions more that array loops to me.

data tri;
   infile cards4 dsd missover;
  
input period:yymmn. var1-var6;
   format period yymmn.;
  
cards;
201401,22223,30303,40000,80000,123444,144000
201402,22334,40002,100202,120000,132000,.
201403,129934,150000,123444,130000,.,.
201404,54349,75000,123444,.,.,.
201501,99900,134423,.,.,.,.
201502,102030,.,.,.,.,.
;;;;
   run;
proc print;
  
run;
data trisum;
   set tri;
   array v
  • var:;
  •    n = n(of v
  • );
      
  • if n lt dim(v) then x = sum(of v
  • );
  •    run;
    proc print;
      
    run;

    Capture.PNG
    brophymj
    Quartz | Level 8

    I ended up using this but it's a bit messy...

    data average;
    input key $4.;
    datalines;
    mean
    run;

    %macro average;

    %do j = 0 %to 36;

    data test&j;
    set test;
    if qtr&j = . then delete;
    key = "mean";
    keep key qtr&j;
    run;

    data test&j;
    set test&j(obs=10);
    run;

    proc summary data = test&j nway missing;
    class key;
    var  qtr&j;
    output out = test&j(drop = _freq_ _type_) mean=;
    run;

    data average;
    merge average test&j;
    by key;
    run;

    %end;
    %mend;

    %average;

    data_null__
    Jade | Level 19

    I don't understand what you want.  For the data you show what values are summed?   You imply there is more than one upper triangular matrix, if so show example and if there is variable to differentiate.

    The way I read your explanation you want

    data _null_;
      
    x = sum(144000,132000,130000,123444,134423,102030);
       put x=;
       run;


    x=765897

    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!

    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.

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