So I have a data box, as follows:
Obs ID A B C D
1 X1 1 . . .
2 X2 1 1 . .
3 X3 . 1 1 .
4 X4 . 1 . .
5 X5 . 1 . .
6 X6 1 . . .
7 X7 1 1 . .
8 X8 1 1 . .
9 X9 . . 1 .
10 X10 1 1 . .
The objective here is to take a sum of the columns i.e
Total=sum(A,B,C,D)
But the problem here is that the number and names of the columns will not be fixed. Therefore, I wanted to create an array which would dynamically store the names of the columns, drop the id column and then wanted to take a sum of the rest. Therefore I wrote a command as follows:
proc sql noprint;
select
name into: cols
from dictionary.columns
where
memname = 'box';quit;
But I get an error as follows:
Statement is not valid or it is used out of proper order.
I'm sure this is not the best way to solve this problem, can anybody help me as to how should I go about it? Thanks a lot in advance.
You're close! The colon should be before the variable after into, and you will want to separate them by a comma. Try this instead:
proc sql noprint;
select name
into :cols separated by ','
from dictionary.columns
where upcase(memname) = 'BOX'
;
quit;