topic Re: How to collapse multiple records into one in SAS Programming

How to collapse multiple records into one

LanMin — Tue, 02 Apr 2013 21:29:51 GMT

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

Re: How to collapse multiple records into one

Patrick — Wed, 03 Apr 2013 01:04:27 GMT

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;

Re: How to collapse multiple records into one

FredrikE — Wed, 03 Apr 2013 14:48:28 GMT

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;

Re: How to collapse multiple records into one

LanMin — Wed, 03 Apr 2013 14:59:25 GMT

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

Lan

Re: How to collapse multiple records into one

LanMin — Fri, 05 Apr 2013 16:06:24 GMT

thanks a lot, FredikE !

Lan