topic Re: monthly sales in a row wise without proc tranpose in SAS Programming

monthly sales in a row wise without proc tranpose

kumarK — Sun, 14 Dec 2014 11:07:42 GMT

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

Prod_id jan feb ... dec

101 1100 500... 800

102 1200 400 500....

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

Please help.

Re: monthly sales in a row wise without proc tranpose

Ksharp — Sun, 14 Dec 2014 11:39:21 GMT

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

Re: monthly sales in a row wise without proc tranpose

Ksharp — Sun, 14 Dec 2014 11:47:41 GMT

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;

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

Re: monthly sales in a row wise without proc tranpose

kumarK — Sun, 14 Dec 2014 12:10:17 GMT

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.

Re: monthly sales in a row wise without proc tranpose

Ksharp — Sun, 14 Dec 2014 13:13:20 GMT

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

Re: monthly sales in a row wise without proc tranpose

slchen — Sun, 14 Dec 2014 15:05:42 GMT

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;

Re: monthly sales in a row wise without proc tranpose

stat_sas — Sun, 14 Dec 2014 21:41:13 GMT

proc tabulate data=have;

format date monname3.;

class prod_id date;

var sales;

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

run;

Re: monthly sales in a row wise without proc tranpose

billfish — Wed, 17 Dec 2014 21:30:00 GMT

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;

Re: monthly sales in a row wise without proc tranpose

billfish — Wed, 17 Dec 2014 22:17:40 GMT

After the statement:

by id;

You need to add the following statement:

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

Re: monthly sales in a row wise without proc tranpose

Cynthia_sas — Thu, 18 Dec 2014 03:55:51 GMT

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

cynthia

Re: monthly sales in a row wise without proc tranpose

art297 — Thu, 18 Dec 2014 05:13:33 GMT

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;