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
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;
Assuming data is sorted by grpname year grpvar1.
Data two; set one; by grpname year grpvar1;
If last.grpvar1 then output;
run;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.