topic Re: Creating a sumproduct - Should be easy in SAS Procedures

Creating a sumproduct - Should be easy

ZColl — Fri, 26 Jun 2015 17:25:38 GMT

OK so I'm new(ish) to SAS. I feel like this is probably very simple and I'm missing something very easy and obvious but for the life of me I cannot figure out why this isn't working. Can anyone help?

Data test;

input obs Condition0001 Condition0002 CCS_1 CCS_2;

Cards;

1 5 10 1 2

2 6 8 2 1

;

Run;

Data test2;

Set test;

Array ArrayCondition[2] Condition0001-Condition0002;

Array ArrayCCS[2] CCS_1-CCS_2;

Sumproduct = 0;

Do i =1 to i = 2;

Sumproduct = sum(Sumproduct, (ArrayCondition*ArrayCCS));

End;

Freq = sum(of ArrayCondition

);

Score = Sumproduct/Freq;

Run;

Basically what I want this to do is add a column "Sumproduct" which should be the sumproduct of my two array's (=Condition0001*CCS_1 + Condition0002*CCS_2).

The code runs fine but the sumproduct ends up being 0 everytime. Is something getting reset in my do statement?

Any help would be extremely appreciated!

Re: Creating a sumproduct - Should be easy

Reeza — Fri, 26 Jun 2015 17:51:03 GMT

What do you want your final output to be?

Depending on the output I would consider using a proc freq with weights and summing those results instead.

Re: Creating a sumproduct - Should be easy

ZColl — Fri, 26 Jun 2015 17:58:02 GMT

Just realized what I was doing wrong. I'm an idiot.

Do i =1 to i = 2; <- WRONG

Do i = 1 to 2; <- RIGHT

Sorry about that. Thanks for looking at it though

Re: Creating a sumproduct - Should be easy

Reeza — Fri, 26 Jun 2015 18:16:00 GMT

Glad it worked out.

I did want to note that you're looking at a dataset using matrix algebra which is better suited to IML not BASE SAS. SAS operates a bit differently than most other statistical packages in that respects.

Re: Creating a sumproduct - Should be easy

ZColl — Fri, 26 Jun 2015 20:34:33 GMT

So the code I posted above is actually for a sample data set. The real data set I have is considerably larger. The code is working for the above sample but for the real dataset it is understating the sumproduct. So I'm wondering do array's in SAS have an element maximum? I was attempting create my arrays over 283 variables. I did some checking work in excel and it looks like the sumproduct SAS is returning is the sumproduct of only the first 247-248 of those columns.

If so, I guess I'll split my arrays into two.

I'm not familiar with IML. I'm doing this for a work project. Generally, I am able to use excel and access for almost everything I do but this dataset was too big for excel and when I tried to use it in Access I learned that Access has a 255 column limit. So I tried my hand at SAS. Not sure if the company offers IML or not.

Re: Creating a sumproduct - Should be easy

Reeza — Fri, 26 Jun 2015 22:04:09 GMT

No limit that I'm aware of, if you use code similar to below do you get the value you get in Excel

data test_flipped;

set test;

array cond(*) condition0001-condition0002;

array ccs(*) ccs_1 - ccs_2;

do i=1 to dim(cond);

condition=cond(i);

ccs_weight=ccs(i);

output;

end;

run;

proc means data=test_flipped sum;

var condition;

weight ccs_weight;

run;

Re: Creating a sumproduct - Should be easy

ZColl — Fri, 26 Jun 2015 22:27:01 GMT

Ok it's running now. I'll let you know if it works when it finishes. In the mean time can you explain to me how this part works:

do i=1 to dim(cond);

condition=cond(i);

ccs_weight=ccs(i);

output;

end;

So I'm assuming the dim(cond) returns the number of elements found in the cond array. Very useful to me as I was looking for something like this earlier.

But I don't understand what the statements inside the do statement are doing. Wouldn't the outcome of that be the final iteration of the do statement since it will override itself each time. Maybe I'm looking at this wrong....

Re: Creating a sumproduct - Should be easy

Reeza — Sat, 27 Jun 2015 23:50:16 GMT

This is using an array method to flip the data so it's long rather than wide. The array loops through the variables and outputs each observation to a new line which can then be processed more easily by proc means and other SAS procs.

data test_flipped;

set test;

array cond(*) condition0001-condition0002;

array ccs(*) ccs_1 - ccs_2;

do i=1 to dim(cond);

condition=cond(i);

ccs_weight=ccs(i);

output;

end;

drop condition0001-condition0002 ccs_1-ccs_2;

run;

Here's a bit of write up on how that works:

SAS Learning Module: Reshaping wide to long using a data step