BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kumarK
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
Ksharp
Super User

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

kumarK
Quartz | Level 8

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.

Ksharp
Super User

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

Ksharp
Super User

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

slchen
Lapis Lazuli | Level 10

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;

billfish
Quartz | Level 8

After the statement:

by id;

You need to add the following statement:

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

stat_sas
Ammonite | Level 13

proc tabulate data=have;

format date monname3.;

class prod_id date;

var sales;

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

run;

Cynthia_sas
SAS Super FREQ

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

cynthia

billfish
Quartz | Level 8

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;

art297
Opal | Level 21

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;

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
  • 10 replies
  • 1992 views
  • 3 likes
  • 7 in conversation