SAS Error: Null parameters for SUM are invalid

0
votes

So I have a data named table1 as follows:

Obs  ID  M_201812  M_201901      M_201902    M_201903

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             .           .

Each column here is a month, which is dynamically generated based on some previously run macro. The months will be dynamic and will vary. What I need to do is calculate sums of last 3 months, last 6 months and last 12 months. The approach I had in my mind was as follows: A) Store the column names in a macro variable:

proc sql noprint;
    select distinct name
    into :cols2 separated by ','  
    from dictionary.columns
    where upcase(memname) = 'Table1' and name not in ('ID');
    ;
quit;
%put &cols2.

The output was as follows:

M_201812,M_201901,M_201902,M_201903

B) Create sums thereafter based on the number of items in the variable:

data table1;
set table1;

if count("&cols2",",") <=3 then do;
3m_total=sum(of &cols2);
6m_total=sum(of &cols2);
12m_total=sum(of &cols2);
end;
else if 3< count("&cols2",",") <=6 then do;
3m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)));
6m_total=sum(of &cols2);
12m_total=sum(of &cols2);
end;
else if 6< count("&cols2",",") <=12 then do;
3m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)));
6m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)),%scan(%superQ(cols2),-4,%str(,)),%scan(%superQ(cols2),-5,%str(,)),%scan(%superQ(cols2),-6,%str(,)));
12m_total=sum(of &cols2);
else do;
    3m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)));
    6m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)),%scan(%superQ(cols2),-4,%str(,)),%scan(%superQ(cols2),-5,%str(,)),%scan(%superQ(cols2),-6,%str(,)));
    12m_total=sum(%scan(%superQ(cols2),-1,%str(,)),%scan(%superQ(cols2),-2,%str(,)),%scan(%superQ(cols2),-3,%str(,)),%scan(%superQ(cols2),-4,%str(,)),%scan(%superQ(cols2),-5,%str(,)),%scan(%superQ(cols2),-6,%str(,)),
    %scan(%superQ(cols2),-7,%str(,)),%scan(%superQ(cols2),-8,%str(,)),%scan(%superQ(cols2),-9,%str(,)),%scan(%superQ(cols2),-10,%str(,)),%scan(%superQ(cols2),-11,%str(,)),%scan(%superQ(cols2),-12,%str(,)));
    end;
    run;

Basically we get 12 months sum only if there are 12 monthly columns available. If only 3 months are available, then 3months sum=6months sum=12months sum. After running the code, I get the following error:

ERROR 159-185: Null parameters for SUM are invalid.

This happens at the last else do statement. I can't for the life of me figure out why won't sas be able to read a simple if-then-do-else statement. Is there an error in the if conditions or in calling the macro variable? Any help here would be appreciated. Thanks a lot.

2
if-statementsassas-macrodatastep
Is there a reason you are starting with that structure? Can't you just start with vertical structure where the date is the value of variable instead of the name of the variable? – Tom
Echoing @Tom, this is a report structure, not a good structure for maintaining or calculating data. It can be done, but is a pain. – Reeza
Echoing @Reeza, specifically, you should keep your data long with MM/YYY column and not wide which helps so much with practically any analysis. See proc transpose. – Parfait

2 Answers

1
votes

Hard to tell without seeing what code your macro logic is actually generating. But most likely it is because you are generating two commas with nothing between them like this:

475   data test;
476     y=sum(1,,2,3);
                -
                159
ERROR 159-185: Null parameters for SUM are invalid.

477   run;

You need to either not generate that extra comma or put something between the commas. So either a period for a missing value or a 0.

In general SAS is easier if you don't use commas. So get a space delimited list of variable names.

proc contents data=Table1 noprint
  out=_contents (where=(name ne 'ID'))
; 
run;

proc sql noprint;
select name
     , case when (varnum>9) then name else ' ' end 
     , case when (varnum>6) then name else ' ' end 
     , case when (varnum>3) then name else ' ' end
  into :cols_12 separated by ' '
     , :cols_3 separated by ' '
     , :cols_6 separated by ' '
     , :cols_9 separated by ' '
  from _contents
  order by name
;
quit;

Then use that list of names using the of keyword so you don't need commas between them.

data want;
  set table1;
  total_3m=sum(0,of &cols_3);
  total_6m=sum(0,of &cols_6);
  total_9m=sum(0,of &cols_9);
  total=sum(0,of &cols_12);
run;

Also remember to use valid names for your variables. Variable names cannot start with numbers.

0
votes

Consider reshaping your data from wide to long with proc transpose and then run multiple correlated aggregate SQL subqueries for 3/6/12-month running sums. This will achieve as you mentioned for desired result:

What I need to do is calculate sums of last 3 months, last 6 months and last 12 months.

Data

data Month_Data;
    infile datalines delimiter=',' DSD; 
    length ID $ 3;
    input ID $ M_201812 M_201901 M_201902 M_201903;
    datalines;
X1, 1, ., ., . 
X2, 1, 1, ., . 
X3, ., 1, 1, . 
X4, ., 1, ., . 
X5, ., 1, ., . 
X6, 1, ., ., . 
X7, 1, 1, ., . 
X8, 1, 1, ., . 
X9, ., ., 1, . 
X10, 1, 1, ., . 
;

Reshape

proc sort data=Month_Data;
    by ID;
run;

proc transpose data=Month_Data
    out=Month_Data_Long;
    by ID;
run;

data Month_Data_Long;
    set Month_Data_Long (rename=(_NAME_ = Month_Year col1=value));
    length MMYY $ 9;
    format Month_Date date9.;

    label Month_Year = "Month Year Original";
    MMYY =  tranwrd(Month_Year, "M_", "") || "01";
    Month_Date = input(MMYY,  yymmdd9.);
run;

Aggregation

proc sql;
    create table Run_Sums as
    select m.ID, 
           m.Month_Date,
           m.Value,
           (select sum(m.Value)
            from Month_Data_Long sub
            where sub.ID = m.ID
             and sub.Month_Date >= intnx('month', m.Month_Date, -3)
             and sub.Month_Date <= m.Month_Date
             and sub.Value ^= .
            ) AS ThreeMonthsSum,

           (select sum(m.Value)
            from Month_Data_Long sub
            where sub.ID = m.ID
             and sub.Month_Date >= intnx('month', m.Month_Date, -6)
             and sub.Month_Date <= m.Month_Date
             and sub.Value ^= .
            ) AS SixMonthsSum,

           (select sum(m.Value)
            from Month_Data_Long sub
            where sub.ID = m.ID
             and sub.Month_Date >= intnx('month', m.Month_Date, -12)
             and sub.Month_Date <= m.Month_Date
             and sub.Value ^= .
            ) AS TwelveMonthsSum

    from Month_Data_Long m;
quit;

Output (no difference in sums due to OP's posted data)

SAS Output