BookmarkSubscribeRSS Feed
TarunKumar
Pyrite | Level 9


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_JanLi_febMF_JanMF_febGI_janGI_feb
a 2551098
b 366662
c 132372
d 441121

and want result like this.

emp_code li_JanLi_febMF_JanMF_febGI_janGI_febJan_SaleFeb_Sale
a 25510981623
b 3666621514
c 132372108
d 44112176
5 REPLIES 5
Amir
PROC Star

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.

TarunKumar
Pyrite | Level 9

Thanx Amir,

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

Amir
PROC Star

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.

Tom
Super User Tom
Super User

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.

Vince28_Statcan
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 629 views
  • 0 likes
  • 4 in conversation