0

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.

IndigoChild
  • 842
  • 3
  • 11
  • 29
  • 1
    If the columns to be summed always form a contiguous block within the column order, you can do `sum(of a--d)`. – user667489 Jun 18 '19 at 14:27
  • Here's a list of ways you can shorten that method of listisng: https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html – Reeza Jun 18 '19 at 14:56
  • Hi User667489..the column names will be dynamic not necessarily in that order. – IndigoChild Jun 18 '19 at 16:46
  • 2
    The metadata values for `LIBNAME` and `MEMNAME` returned by `DICTIONARY.*` tables is always UPPERCASE. – Richard Jun 18 '19 at 19:06

1 Answers1

1

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;
Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21
  • HI Stu, Thanks for the answer. But when I write %put &cols. - I get WARNING: Apparent symbolic reference COLS not resolved. Any Idea why? – IndigoChild Jun 18 '19 at 16:46