calculating observation per firm year.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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
Respected Advisor
Posts: 3,156

Re: calculating observation per firm year.

Posted in reply to rilatotoro

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

View solution in original post


All Replies
Solution
‎04-30-2012 08:56 AM
Respected Advisor
Posts: 3,156

Re: calculating observation per firm year.

Posted in reply to rilatotoro

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1364 views
  • 0 likes
  • 2 in conversation