How to collapse multiple records into one

[LanMin]

Hi, all
I need your help programming the following
I want
1. combine multiple records into one if each record has the same GVKEY, SICS, and data_date, and use the sum of its sales as the sales for the single record and keep the first SID number.
2. Create a year variable based on data_date, if data_date month is before May 31, year variable=year (year variable)-1, otherwise year variable=year (year variable)- should I use month function and if then?

Data have
GVKEY SID data_date SICS SALES
001004 1 19970531 4582 46.14
001004 2 19970531 4582 34.395
001004 1 19980531 4582 53.16
001004 2 19980531 4582 39.66
001005 3 19981031 3412 189.897
001005 4 19981031 3443 26.328
001005 5 19981031 5088 31.787

Data want
GVKEY SID data_date SICS SALES
001004 1 19970531 4582 92.82
001004 1 19980531 4582 80.535
001005 3 19981031 3412 189.897
001005 4 19981031 3443 26.328
001005 5 19981031 5088 31.787

thanks !

Lan

[Patrick]

Some code like below should do.

proc sql;

  create table want as

  select

    GVKEY

    ,SICS

    ,data_date format=date9.

    , min(sid) as sid

    , sum(sales) as sales

    , year(intnx('year.6',data_date,0,'b')) as year

  from have

  group by GVKEY,SICS,data_date

  ;

quit;

[LanMin]

thanks a lot, Patrick. your help is very much aprpeciated!

Lan

[FredrikE]

How about this?

data a;
length gvkey $6 sid 8 data_date $8 sics 8 sales 8;
input GVKEY SID data_date SICS SALES;

datalines;
001004 1 19970531 4582 46.14
001004 2 19970531 4582 34.395
001004 1 19980531 4582 53.16
001004 2 19980531 4582 39.66
001005 3 19981031 3412 189.897
001005 4 19981031 3443 26.328
001005 5 19981031 5088 31.787
;
run;

proc sort data=a;
by GVKEY SICS data_date SID;
run;

data b;
set a;
retain first_sid sales_sum;
by GVKEY SICS data_date SID;

if month(input(data_date,anydtdte.)) lt 5 then year = year(input(data_date,anydtdte.))-1;
else year = year (input(data_date,anydtdte.));

if first.data_date then do;
   first_sid=sid;
   sales_sum = sales;
end;
else do;
   sales_sum=sum(sales_sum,sales);
end;

if last.data_date then do;
  output;
end;

run;

[LanMin]

thanks a lot, FredikE !

Lan