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
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;
thanks a lot, Patrick. your help is very much aprpeciated!
Lan
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;
thanks a lot, FredikE !
Lan
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.