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
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.
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.