topic Re: sum group by in if then do in SAS Programming

sum group by in if then do

skavli — Thu, 08 Oct 2020 13:57:59 GMT

Hi I hope you're all well,

I have a simple problem yet quite disturbing as I don't know what happens exactly and why don't I get the results wanted, to put you in the context let's suppose you have a table with variables like this ;

project company category ROI contribution

My aim is quite simple, i wanna sum my contibs according to certain companies by project and category ( i'm sorry i can't give you the right names nor values of my table for confidentiality purposes ).

Now since i want only certain clients sums I did this : ( assume the company is Shelbey LTD )

proc sort data=table2; by  project category ;run;

options mprint;
data table3;
set table2;
Company = scan(project,1,'_'); /* from here i get my company name */
by project category;
if Company = "SHELBEY LTD" then do;
	if First.contrib then contrib2=0;
		contrib + contrib2;
	if Last.contrib;
end;
run;

Well, in the results all my companies are there with no value in contrib2 ( which is normal considering the fact that i "selected" only the shelbey company ) but all the shelbey observations disappeard !

It's quite weird, this the first time i encounter this kind of difficulty.

here is the message i got from the log

NOTE: Variable 'First.contrib'n is uninitialized.
NOTE: Variable 'Last.contrib'n is uninitialized.
NOTE: There were 1052 observations read from the data set WORK.TABLE2.
NOTE: The data set WORK.TABLE3 has 860 observations and 13 variables.
NOTE: Compressing data set WORK.TABLE3 decreased size by 83.33 percent. 
      Compressed is 2 pages; un-compressed would require 12 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Please Help if you can.

Thanks in advance and stay safe 🙂

Re: sum group by in if then do

gamotte — Thu, 08 Oct 2020 14:02:03 GMT

Hello,
You use first.contrib and last.contrib but contrib is not in your by statement.

Re: sum group by in if then do

gamotte — Thu, 08 Oct 2020 14:13:57 GMT

I think you should use first.category and last.category instead of first.contrib and last.contrib.

I guess you probably also want to change :

contrib+contrib2;

into

contrib2+contrib;

That is contrib2 is the sum of contrib column for the current project/category.

Re: sum group by in if then do

skavli — Thu, 08 Oct 2020 14:22:35 GMT

why is the second order important ?

Re: sum group by in if then do

skavli — Thu, 08 Oct 2020 14:26:25 GMT

Oh yes, I realized that i have loop over the occurences in my by group not the values that corresponds to it thanks !

Re: sum group by in if then do

gamotte — Thu, 08 Oct 2020 14:31:05 GMT

COL1+COL2;

is a short way to write :

retain COL1;

COL1=sum(COL1, COL2);

So the leftmost operand should be the name of the column that will contain the summation result.