calculating observation per firm year.

Solved
Occasional Contributor
Posts: 17

calculating observation per firm year.

Hi everyone,

I'm totally new to SAS and i'm trying to get an assignment done. Your help is deeply appreciated.

No. of segments per year

Company A        Segment 1                1990                                    3

Company A        Segment 2                1990                                    3

Company A        Segment 3                1990                                    3

Company A        segment 1                1991                                     3

Company A        segment 2                1991                                     3

Company A        segment 3                1991                                     3

.

.

.

.

Company B       Segment 1                 1990                                   4

Company B       Segment 2                 1990                                   4

Company B       Segment 3                 1990                                   4

Company B       Segment 4                 1990                                   4

Basically I'm trying to create the "No. of segments per year" column on the right hand side. Any idea as to how I can actually do it?

What I'm trying to do is basically separate out single segment firms from multi segment firms in each year. If there is a more efficient method please "educate" me. Thank you so much!

Accepted Solutions
Solution
‎04-30-2012 08:56 AM
Posts: 3,167

Re: calculating observation per firm year.

Proc SQL seems to meet your need, if you need to use data step, then 2X DOW can do what you want:

data have;

input (company segment) (:\$&10.) year\$;

cards;

Company A Segment 1 1990 3

Company A Segment 2 1990 3

Company A Segment 3 1990 3

Company A segment 1 1991 3

Company A segment 2 1991 3

Company A segment 3 1991 3

Company B Segment 1 1990 4

Company B Segment 2 1990 4

Company B Segment 3 1990 4

Company B Segment 4 1990 4

;

proc sql;

create table want as

select *, count(*) as No_of_segment from have

group by company, year

;

quit;

proc print;run;

/*2XDOW solution:*/

data want;

do _n_=1 by 1 until (last.year);

set have;

by company year;

end;

do until (last.year);

set have;

by company year;

No_of_segment=_n_;

output;

end;

run;

Regards,

Haikuo

All Replies
Solution
‎04-30-2012 08:56 AM
Posts: 3,167

Re: calculating observation per firm year.

Proc SQL seems to meet your need, if you need to use data step, then 2X DOW can do what you want:

data have;

input (company segment) (:\$&10.) year\$;

cards;

Company A Segment 1 1990 3

Company A Segment 2 1990 3

Company A Segment 3 1990 3

Company A segment 1 1991 3

Company A segment 2 1991 3

Company A segment 3 1991 3

Company B Segment 1 1990 4

Company B Segment 2 1990 4

Company B Segment 3 1990 4

Company B Segment 4 1990 4

;

proc sql;

create table want as

select *, count(*) as No_of_segment from have

group by company, year

;

quit;

proc print;run;

/*2XDOW solution:*/

data want;

do _n_=1 by 1 until (last.year);

set have;

by company year;

end;

do until (last.year);

set have;

by company year;

No_of_segment=_n_;

output;

end;

run;

Regards,

Haikuo

Occasional Contributor
Posts: 17

Re: calculating observation per firm year.

It seems to be working. Thank you so much!

🔒 This topic is solved and locked.