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.
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;
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
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.
No. SQL is not good for transposing data. I would like use proc transpose if the table is not big.
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
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;
After the statement:
by id;
You need to add the following statement:
if first.id then call missing(of mon(*));
proc tabulate data=have;
format date monname3.;
class prod_id date;
var sales;
table prod_id,date=' '*sales=' '*sum=' ';
run;
I agree, either TABULATE or REPORT is a better choice than MEANS or Data step.
cynthia
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.