Array of Column Variables in SQL

Question

Is it possible to create an array of column variables within sql to perform an operation like the following (please excuse the syntax):

array(Col1,Col2,Col3);

update tempTable
    for(i=1;i<3;i++){
        set array[i] = 
            case missing(array[i]) 
                then 0*1
            else
                array[i]*1
            end
};

note: I am using a proc SQL step in SAS

Desired function: Perform the operation in the for loop above on multiple columns of a table, without writing a separate set statement for each column.

Why use SQL for this? Why not just write a normal DATA step? — Tom
– Tom, Commented Aug 16, 2018 at 4:03

DomPazz · Accepted Answer · 2018-08-15 19:45:59Z

3

It is possible to do what you are looking for with a SAS macro.

It is easier, if this is a local SAS table, to just update it with the Data Step.

data have;
set have;
array v[3] col1 col2 col3;

do i=1 to 3;
    v[i] = sum(v[i],0);
end;

drop i;
run;

The sum() function sums values (obviously). If a value is missing, it is not added and the remaining values are added. So you will get 0 in the case of missing and the value of the column when it is not.

answered Aug 15, 2018 at 19:45

DomPazz

12.4k19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ericauv Over a year ago

Thanks! However is it possible to do this using SQL?

Reeza Over a year ago

Not easily. You could write a macro to basically create the multiple SQL codes though, just not usually worth the effort.

DomPazz · Accepted Answer · 2018-08-15 20:38:35Z

SAS Macros write SAS code for you. They are pre-compiler scripts that generate SAS Code.

You want code that looks like

update table
   set col1 = ...  ,
       col2 = ...  ,
       ....        ,
   ;

Here is a script. It generates a test table, defines the macro, and then calls the macro on the table. It uses the sum() function from my other answer.

data have;
array col[3];

do r=1 to 100;
    do i=1 to 3;
        if ranuni(123)> .8 then
            col[i] = .;
        else
            col[i] = rannor(123);
    end;
    output;
end;
drop i r;
run;

%macro sql_zero_if_missing(data, cols);
%local n i col;
%let n=%sysfunc(countw(&cols));

proc sql noprint;
update &data
    set
    %do i=1 %to &n;
        %let col=%scan(&cols,&i);
        &col = sum(&col,0)
        %if &i ^= &n %then , ;
    %end;
;
quit;

%mend;

options mprint;
%sql_zero_if_missing(have, col1 col2 col3);

The MPRINT option will let you see the SAS code that was generated. Here is the log:

MPRINT(SQL_ZERO_IF_MISSING): proc sql noprint;

MPRINT(SQL_ZERO_IF_MISSING): update have set col1 = sum(col1,0) , col2 = sum(col2,0) , col3 = sum(col3,0) ;

NOTE: 100 rows were updated in WORK.HAVE.

MPRINT(SQL_ZERO_IF_MISSING): quit;

Collectives™ on Stack Overflow

Array of Column Variables in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related