SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Collapsing longitudinal data at the individual level to group level

Reply
Occasional Contributor
Posts: 7

Collapsing longitudinal data at the individual level to group level

Hi, I've searched repeatedly, but can't seem to find a solution to what should be a relatively simple issue.  I have individual-level data that contains some group-level variables.  What I'd like to do is extract the group-level data, on an annual basis.  So, for example, the data I have in hand looks similar to:

YearGroup NameGroup Var 1Group Var 2Indiv NameIndiv Var 1Indiv Var 2
2005AlphaNY500James5BA
2005AlphaNY500Mark8MD
2005AlphaNY500Sally12BS
2006AlphaNY480James3BA
2006AlphaNY480Sally9BS
2005BetaCA875Jane7MS
2005BetaCA875Joe8BA
2006BetaCA900Jane9BS
2006BetaCA900Joe10BA
2006BetaCA 900Margaret11JD

And what I need to do is create a dataset that contains only a single observation, by year at the group level (after deleting any individual-level variables).  In other words, what I'm seeking to create is a dataset that looks like:

YearGroup NameGroup Var 1Group Var 2
2005AlphaNY500
2006AlphaNY480
2005BetaCA875
2006BetaCA900

My thought was that using FIRST.var together with BY group would work, but it hasn't.  (Also using two FIRST.s -- e.g., FIRST.Group_Name AND FIRST.Year -- f  Any thoughts/suggestions are appreciated!

M Gray

Super User
Posts: 10,497

Re: Collapsing longitudinal data at the individual level to group level

Proc sql;

     create table want as

     select distinct year, groupname, groupvar1, groupvar2 /* continue as needed, note the commas are needed*/

     from have

     order by year, groupname;

quit;

Frequent Contributor
Posts: 83

Re: Collapsing longitudinal data at the individual level to group level

Assuming data is sorted by grpname year grpvar1.

Data two;   set one;    by grpname year grpvar1;

If last.grpvar1 then output;

  run;

Occasional Contributor
Posts: 15

Re: Collapsing longitudinal data at the individual level to group level

This will provide you the expected result

proc sort data=company;

    by  Year Group_name;

run;

data new(keep=Year Group_Name Group_var_1 Group_var_2 First_Year Last_Year First_Group_Name First_Group_name last_Group_name);

    set company;

    by Year Group_name;

    if last.Group_name;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 322 views
  • 0 likes
  • 4 in conversation