DATA Step, Macro, Functions and more

monthly sales in a row wise without proc tranpose

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

monthly sales in a row wise without proc tranpose

Hi,

I have a data set like this

prod-id date sales

101 01/15/2014 500

101 01/15/2014 600

101 02/12/2014 500

101 12/15/2014 800

102 01/18/2014 600

102 01/15/2014 600

102 02/12/2014 400

102 03/15/2014 500

i want product wise monthly sales in a row

like

Prod_id jan      feb ...     dec

101       1100     500...     800         

102        1200     400   500....

in data step(without proc transpose) and sql queries.

Please help.


Accepted Solutions
Solution
‎12-18-2014 12:13 AM
PROC Star
Posts: 7,363

Re: monthly sales in a row wise without proc tranpose

data want;

  set have;

  array Mth jan feb mar apr may jun jul aug sep oct nov dec;

  by prod_id;

  if first.prod_id then call missing(of Mth(*));

  Mth(month(date))+Sales;

  if last.prod_id then output;

run;

View solution in original post


All Replies
Super User
Posts: 9,681

Re: monthly sales in a row wise without proc tranpose

Why ? Why couldn't use proc transpose or SQL?

data have;
input id date : mmddyy10. sales     ;
cards;
101 01/15/2014 500
101 01/15/2014 600
101 02/12/2014 500
101 12/15/2014 800
102 01/18/2014 600
102 01/15/2014 600
102 02/12/2014 400
102 03/15/2014 500
;
run;
proc summary data=have nway;
 class id date;
 format date monyy7.;
 var sales;
 output out=temp(drop=_:) sum=;
run;
proc sql;
 select distinct catt('temp(where=(id=',id,' and year(date)=',year(date),' and month(date)=',month(date),')
                            rename=(sales=',put(mdy(month(date),1,year(date)),monyy7.),'))') 
                     into : list separated by ' ' from temp;
quit;
%put &list;
data want;
 merge &list ;
 by id;
 drop date;
run;

Xia Keshan

Frequent Contributor
Posts: 84

Re: monthly sales in a row wise without proc tranpose

Thanks for reply. In one of the interview he asked me without using transpose. Thanksalot for your time.

we can use sql queries.can u please suggest by sql query.

Super User
Posts: 9,681

Re: monthly sales in a row wise without proc tranpose

No. SQL is not good for transposing data. I would like use proc transpose if the table is not big.

Super User
Posts: 9,681

Re: monthly sales in a row wise without proc tranpose

data have;

input id date : mmddyy10. sales     ;

cards;

101 01/15/2014 500

101 01/15/2014 600

101 02/12/2014 500

101 12/15/2014 800

102 01/18/2014 600

102 01/15/2014 600

102 02/12/2014 400

102 03/15/2014 500

;

run;

proc summary data=have nway;

class id date;

format date monyy7.;

var sales;

output out=temp(drop=_Smiley Happy sum=;

run;

data x;

set temp;

d=put(date,monyy7.);

drop date;

run;

data _null_;

set x end=last;

if _n_ eq 1 then call execute('data want;merge ');

call execute(catt('x(where=(id=',id,' and d="',d,'") rename=(sales=',d,'))'));

if last then call execute('; by id;run;');

run;

Xia Keshan

Super Contributor
Posts: 275

Re: monthly sales in a row wise without proc tranpose

Supposed that you have more sales in different months, try this.

data want;

   do i=1 by 1 until (last.id);

   set have;

   by id;

   array mon  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;

   n=month(date);

   mon(n)+sales;

   if last.id then output;

   end;

   format date mmddyy10.;

   drop n;

   run;

Contributor
Posts: 52

Re: monthly sales in a row wise without proc tranpose

After the statement:

by id;

You need to add the following statement:

if first.id then call missing(of mon(*));

Trusted Advisor
Posts: 1,204

Re: monthly sales in a row wise without proc tranpose

proc tabulate data=have;

format date monname3.;

class prod_id date;

var sales;

table prod_id,date=' '*sales=' '*sum=' ';

run;

SAS Super FREQ
Posts: 8,743

Re: monthly sales in a row wise without proc tranpose

I agree, either TABULATE or REPORT is a better choice than MEANS or Data step.

cynthia

Contributor
Posts: 52

Re: monthly sales in a row wise without proc tranpose

First, I simulate your "have" table:

data have(keep=id zDate zMth zSales);
   dt_0='1jan2014'd;

   do id = 101 to 120;
   do j = 1 to 20;
     zDate = dt_0 +int(360*ranuni(3));
     zMth = month(zDate);
     zSales = int(100*ranuni(3));
     output;
   end;
   end;
run;

proc sort data=have; by id zDate; run;

If one is looking at sales for only a given calendar year, then a solution is:

data want(keep=id jan--dec);
   length id 8.;
   array Mth jan feb mar apr may jun jul aug sep oct nov dec;
   retain _all_;

   set have;
   by id zMth;
  
   if first.id then call missing(of Mth(*));
   if first.zMth then TSales=0;
      TSales+zSales;
   if last.zMth then Mth(zMth)=TSales;
   if last.id then output;
run;

Solution
‎12-18-2014 12:13 AM
PROC Star
Posts: 7,363

Re: monthly sales in a row wise without proc tranpose

data want;

  set have;

  array Mth jan feb mar apr may jun jul aug sep oct nov dec;

  by prod_id;

  if first.prod_id then call missing(of Mth(*));

  Mth(month(date))+Sales;

  if last.prod_id then output;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 640 views
  • 3 likes
  • 7 in conversation