[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    SAS-L: Array processing in SQL (sort of) academic exercise)
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-06-26 19:19:17
Message-ID: 7930993681677618.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

SAS-L: Array processing in SQL (sort of)  academic exercise

Summing and array of 100 elements inside 'proc sql'

  WORKING CODE
  ============

    %let rec=0;
        select
           dosubl('
             %let rec=%eval(&rec+1);
             data have;
               set class(firstobs=&rec obs=&rec);
               array xs[100] x1-x100 (100*1);
               tot=sum(of xs[*])/&rec;
               call symputx("tot",put(tot,6.2));
             run;quit;
           ') as dum length=3
          ,name
          ,symget("tot")   as tot
        from
           class


HAVE  ( sashelp.class and an array of x1-x100)
==============================================

Middle Observation(1 ) of Last dataset = WORK.HAVE - Total Obs 19


 -- CHARACTER --             SAMPLE VALUE
NAME              C    8       Alfred
SEX               C    1       M


 -- NUMERIC --
X1                N    8       1
X2                N    8       1
X3                N    8       1
X4                N    8       1
....

X97               N    8       1
X98               N    8       1
X99               N    8       1
X100              N    8       1

AGE               N    8       14
HEIGHT            N    8       69
WEIGHT            N    8       112.5


 NAME      SEX    AGE    HEIGHT    WEIGHT      X1 X2 X3 X4 X5  ..  X100

Alfred      M      14     69        112.5       1  1  1  1  1        1
Alice       F      13     56.5       84.0       1  1  1  1  1        1
Barbara     F      13     65.3       98.0       1  1  1  1  1        1
Carol       F      14     62.8      102.5       1  1  1  1  1        1




WANT
====

Up to 40 obs WORK.WANT total obs=19

Obs    NAME       AGE      TOT

  1    Alfred      14     100.00   sum(of xs[*])/1 = 100
  2    Alice       13     50.00    sum(of xs[*])/2 =  50
  3    Barbara     13     33.33    sum(of xs[*])/3 =  33.33
  4    Carol       14     25.00    sum(of xs[*])/4 =  25
  5    Henry       14     20.00    sum(of xs[*])/5 =  20

*                _              _       _
 _ __ ___   __ _| | _____    __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \  / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/ | (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|  \__,_|\__,_|\__\__,_|

;

data class;
   set sashelp.class;
   array xs[100] x1-x100 (100*1);
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

%let rec=0;
proc sql;
   create
     table want(drop=dum) as
   select
     dosubl('
       %let rec=%eval(&rec+1);
       %put &=rec;
       data have;
         set class(firstobs=&rec obs=&rec);
         array xs[100] x1-x100 (100*1);
         tot=sum(of xs[*])/&rec;
         call symputx("tot",put(tot,6.2));
       run;quit;
     ') as dum length=3
    ,name
    ,symget("tot")   as tot
    ,age

   from
     sashelp.class
;quit;
[prev in list] [next in list] [prev in thread] [next in thread]