topic Re: Arrays in Proc SQL in New SAS User

Arrays in Proc SQL

KristinaT — Tue, 05 Nov 2019 08:53:49 GMT

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

Re: Arrays in Proc SQL

rudfaden — Tue, 05 Nov 2019 09:11:40 GMT

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

Re: Arrays in Proc SQL

Kurt_Bremser — Tue, 05 Nov 2019 09:17:47 GMT

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

Re: Arrays in Proc SQL

LinusH — Tue, 05 Nov 2019 09:52:29 GMT

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

Re: Arrays in Proc SQL

Kurt_Bremser — Tue, 05 Nov 2019 10:05:07 GMT

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;

Re: Arrays in Proc SQL

Reeza — Tue, 05 Nov 2019 16:20:59 GMT

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