Arrays and Proc SQL Question

From sasCommunity
Jump to: navigation, search

Q: How can I get this Array:

ARRAY BLANK (12) 
&MON12._HITS &MON11._HITS &MON10._HITS &MON09._HITS &MON08._HITS &MON07._HITS &MON06._HITS &MON05._HITS &MON04._HITS &MON03._HITS &MON02._HITS &MON01._HITS; 
DO i = 1 TO 12; 
IF BLANK(i) = . THEN BLANK(i) = 0; 
END;

Into this SQL statement:

PROC SQL; 
CREATE TABLE COMBINED AS 
SELECT COALESCE(ONE.ID,&MON12..ID) 
LABEL='ID', 
&MON12._HITS+&MON11._HITS+&MON10._HITS+ &MON09._HITS+&MON08._HITS, &MON07._HITS+&MON06._HITS+ &MON05._HITS+&MON04._HITS+ &MON03._HITS+&MON02._HITS+ &MON01._HITS 
AS TOTAL_HITS, 
FROM ONE FULL JOIN &MON12 
ON ONE.ID = &MON12..ID; 
QUIT;

A: You can't. PROC SQL has nothing analogous to the DATA step array, and no looping capability.

In the particular circumstances of the example, use the SUM function instead of the addition operator (+) to construct the totals. That eliminates the need to substitute zeroes for missing values.

In general, you have to code repetitiously for each column in the set. This can be avoided by shaping the table so that each series of 12 values is presented vertically rather than horizontally.