BookmarkSubscribeRSS Feed
MGray000
Calcite | Level 5

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

3 REPLIES 3
ballardw
Super User

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;

Jim_G
Pyrite | Level 9

Assuming data is sorted by grpname year grpvar1.

Data two;   set one;    by grpname year grpvar1;

If last.grpvar1 then output;

  run;

samirt
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 939 views
  • 0 likes
  • 4 in conversation