topic Re: Sum of variable in SAS Programming

Sum of variable

TarunKumar — Mon, 18 Nov 2013 12:06:44 GMT

Dear Freind,

i want to sum data of mulitiple product of diff diff month on certain condition. for example i have multiple product and sales number. I want to find out total sale for Jan, feb mar.....

emp_code	li_Jan	Li_feb	MF_Jan	MF_feb	GI_jan	GI_feb
a	2	5	5	10	9	8
b	3	6	6	6	6	2
c	1	3	2	3	7	2
d	4	4	1	1	2	1

and want result like this.

emp_code	li_Jan	Li_feb	MF_Jan	MF_feb	GI_jan	GI_feb	Jan_Sale	Feb_Sale
a	2	5	5	10	9	8	16	23
b	3	6	6	6	6	2	15	14
c	1	3	2	3	7	2	10	8
d	4	4	1	1	2	1	7	6

Re: Sum of variable

Amir — Mon, 18 Nov 2013 12:22:13 GMT

Hi,

How about something like:

data want;

set have;

jan_sale=sum(0,li_jan,mf_jan,gi_jan);

feb_sale=sum(0,li_feb,mf_feb,gi_feb);

mar_sale=sum(0,li_mar,mf_mar,gi_mar);

/* etc...*/

run;

You mentioned "on certain condition", this could be put in an if statement.

Regards,

Amir.

Re: Sum of variable

Vince28_Statcan — Mon, 18 Nov 2013 12:25:16 GMT

Hi,

You can use arrays with semi columns to scope a variable range. If you know your column structures, you could also create giant arrays and run with relative index increment to code your own sums without having to code nearly as many arrays. It will take fewer lines of code but the underlying logic is slightly more difficult to achieve. Ultimately, even if it's only 12 months, you can use macros to save yourself some trouble e.g.

%let m1=jan;

%let m2=feb;

%let m3=mar;

%let m4=apr;

%let m5=mai;

%let m6=jun;

%let m7=jul;

%let m8=aug;

%let m9=sep;

%let m10=oct;

%let m11=nov;

%let m12=dec;

%macro sums();

data want;

set have;

%do i=1 %to 12;

array &&x&i.. {*} :_&&x&i.. ;

%end; /* column is the generic char for "anything before" _jan _feb etc. - we just declared 12 arrays, one per month, that include all products of a given month */

%do i=1 %to 12;

&&x&i.._SALE = sum(of &&x&i..); /*sum of the array defined by the same name */

%end;

run;

%mend;

%sums();

If you need further help for special conditions that can't seem to be handled with variables naming, feel free to add more details to your question.

Re: Sum of variable

TarunKumar — Mon, 18 Nov 2013 12:25:54 GMT

Thanx Amir,

i have huge data and this is very lengthy manual process. is it possibe by using array or macro.

Re: Sum of variable

Amir — Mon, 18 Nov 2013 13:59:33 GMT

Hi,

If the post by does not help, then perhaps you could provide more information about your data, for example can you have more than one observation per emp_code? what is the real number of columns you need to sum up for each month? how many observations do you have in the data set? what was the "certain condition" you referred to? etc.

Regards,

Amir.

Re: Sum of variable

Tom — Mon, 18 Nov 2013 17:01:29 GMT

You can generate the code from the list of variables.

data have;

input emp_code $ li_Jan Li_feb MF_Jan MF_feb GI_jan GI_feb ;

cards;

a 2 5 5 10 9 8

b 3 6 6 6 6 2

c 1 3 2 3 7 2

d 4 4 1 1 2 1

run;

proc sql noprint ;

create table varlist as

select upcase(scan(name,-1,'_')) as month

, name

from dictionary.columns

where libname='WORK' and memname='HAVE'

and calculated month in

('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

order by 1,2

;

run;

filename code temp;

data _null_ ;

set varlist ;

by month ;

file code ;

if first.month then put month +(-1) '_Sales=sum(0' @;

put ',' name @ ;

if last.month then put ');' ;

run;

data want ;

set have ;

%inc code / source2 ;

run;

288 data want ;

289 set have ;

290 %inc code / source2 ;

NOTE: %INCLUDE (level 1) file CODE is file .../#LN00022.

291 +FEB_Sales=sum(0,GI_feb ,Li_feb ,MF_feb );

292 +JAN_Sales=sum(0,GI_jan ,MF_Jan ,li_Jan );

NOTE: %INCLUDE (level 1) ending.

293 run;

NOTE: There were 4 observations read from the data set WORK.HAVE.

NOTE: The data set WORK.WANT has 4 observations and 9 variables.