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;
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.
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.