BookmarkSubscribeRSS Feed
LanMin
Fluorite | Level 6

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

4 REPLIES 4
Patrick
Opal | Level 21

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
Fluorite | Level 6

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

Lan

FredrikE
Rhodochrosite | Level 12

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
Fluorite | Level 6

thanks a lot, FredikE !

Lan

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 828 views
  • 0 likes
  • 3 in conversation