Arrays in Proc SQL

[KristinaT]

Hello there, I am relatively new to SAS. Until now I've been mainly using SAS wizards in SAS Enterprise Guide.

Recently I got a got code from colleague with proc SQL.

It is quite repetitive, so I tried to amend using arrays, but with no success. Is that possible at all. Here is the code:

proc sql;

   select Year,

count(clientID) as Disbursed,

sum(MOB1_30) as MOB1 , sum(MOB2_30) as MOB2 , sum(MOB3_30) as MOB3 ,

sum(MOB4_30) as MOB4 , sum(MOB5_30) as MOB5 , sum(MOB6_30) as MOB6 ,

sum(MOB7_30) as MOB7 , sum(MOB8_30) as MOB8 , sum(MOB9_30) as MOB9 ,

sum(MOB10_30) as MOB10 , sum(MOB11_30) as MOB11 , sum(MOB12_30) as MOB12 , sum(MOB13_30) as MOB13 ,

sum(MOB14_30) as MOB14 , sum(MOB15_30) as MOB15 , sum(MOB16_30) as MOB16 , sum(MOB17_30) as MOB17 ,

sum(MOB18_30) as MOB18 , sum(MOB19_30) as MOB19 , sum(MOB20_30) as MOB20 , sum(MOB21_30) as MOB21 ,

sum(MOB22_30) as MOB22 , sum(MOB23_30) as MOB23 , sum(MOB24_30) as MOB24 , sum(MOB25_30) as MOB25

from WORK.WorkingFile group by Year ;

Thank you,

Kristina

[rudfaden]

You cannot use arrays in proc sql. Only in a datastep.

[Kurt_Bremser]

SQL does not have the concept of arrays.

You can use a macro to create the repeating code automatically:

%macro help_me;

proc sql;
select
  Year,
  count(clientID) as Disbursed
%do i = 1 %to 25;
  ,sum(MOB&i._30) as MOB&i.
%end;
from WORK.WorkingFile group by Year;

%mend;

%help_me

[LinusH]

This type of case typically calls for transformaing your data, from wide to long.

[Kurt_Bremser]

And you should seriously consider to structure your variable names differently, as you can then use wildcards or variable lists:

proc summary data=workingfile nway sum;
class year account;
var mob30_1-mob30_25;
output
  out=int
  sum(mob30_1-mob30_25)=
;
run;

proc summary data=int nway sum;
class year;
var mob30_1-mob30_25;
output
  out=want (
    drop=_type_
    rename=(_freq_=disbursed)
  )
  sum(mob30_1-mob30_25)=
;
run;

[Reeza]

SQL doesn't support arrays or variable lists. 

PROC MEANS is a proc designed to calculate summary statistics and will be easier to run than your current approach. It's dynamic so if the number of variables change it can as well.

---

@KristinaT wrote:

Hello there, I am relatively new to SAS. Until now I've been mainly using SAS wizards in SAS Enterprise Guide.

Recently I got a got code from colleague with proc SQL.

It is quite repetitive, so I tried to amend using arrays, but with no success. Is that possible at all. Here is the code:

proc sql;

   select Year,

count(clientID) as Disbursed,

sum(MOB1_30) as MOB1 , sum(MOB2_30) as MOB2 , sum(MOB3_30) as MOB3 ,

sum(MOB4_30) as MOB4 , sum(MOB5_30) as MOB5 , sum(MOB6_30) as MOB6 ,

sum(MOB7_30) as MOB7 , sum(MOB8_30) as MOB8 , sum(MOB9_30) as MOB9 ,

sum(MOB10_30) as MOB10 , sum(MOB11_30) as MOB11 , sum(MOB12_30) as MOB12 , sum(MOB13_30) as MOB13 ,

sum(MOB14_30) as MOB14 , sum(MOB15_30) as MOB15 , sum(MOB16_30) as MOB16 , sum(MOB17_30) as MOB17 ,

sum(MOB18_30) as MOB18 , sum(MOB19_30) as MOB19 , sum(MOB20_30) as MOB20 , sum(MOB21_30) as MOB21 ,

sum(MOB22_30) as MOB22 , sum(MOB23_30) as MOB23 , sum(MOB24_30) as MOB24 , sum(MOB25_30) as MOB25

from WORK.WorkingFile group by Year ;

Thank you,

Kristina

---