topic Re: Collapsing longitudinal data at the individual level to group level in SAS Data Management

Collapsing longitudinal data at the individual level to group level

MGray000 — Wed, 26 Aug 2015 14:24:48 GMT

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:

Year	Group Name	Group Var 1	Group Var 2	Indiv Name	Indiv Var 1	Indiv Var 2
2005	Alpha	NY	500	James	5	BA
2005	Alpha	NY	500	Mark	8	MD
2005	Alpha	NY	500	Sally	12	BS
2006	Alpha	NY	480	James	3	BA
2006	Alpha	NY	480	Sally	9	BS
2005	Beta	CA	875	Jane	7	MS
2005	Beta	CA	875	Joe	8	BA
2006	Beta	CA	900	Jane	9	BS
2006	Beta	CA	900	Joe	10	BA
2006	Beta	CA	900	Margaret	11	JD

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:

Year	Group Name	Group Var 1	Group Var 2
2005	Alpha	NY	500
2006	Alpha	NY	480
2005	Beta	CA	875
2006	Beta	CA	900

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

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

ballardw — Wed, 26 Aug 2015 15:07:10 GMT

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;

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

Jim_G — Wed, 26 Aug 2015 15:54:50 GMT

Assuming data is sorted by grpname year grpvar1.

Data two; set one; by grpname year grpvar1;

If last.grpvar1 then output;

run;

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

samirt — Mon, 31 Aug 2015 07:03:34 GMT

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;