DATA Step, Macro, Functions and more

How to collapse multiple records into one

Reply
Frequent Contributor
Posts: 102

How to collapse multiple records into one

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

Respected Advisor
Posts: 4,173

Re: How to collapse multiple records into one

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;

Frequent Contributor
Posts: 102

Re: How to collapse multiple records into one

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

Lan

Regular Contributor
Posts: 191

Re: How to collapse multiple records into one

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;

Frequent Contributor
Posts: 102

Re: How to collapse multiple records into one

thanks a lot, FredikE !

Lan

Ask a Question
Discussion stats
  • 4 replies
  • 263 views
  • 0 likes
  • 3 in conversation