DATA Step, Macro, Functions and more

keep only common observations in the same period (year)

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

keep only common observations in the same period (year)

Hello Genius ! 

I need Your help. It's my first participation here. In fact Im a beginner and I'm learning SAS. I have data grouped in columns and associated with years.

just like this form:

                                                     
     Var                year
                                  
    Tr1           2006                  
    Tr2           2006                         
    Tr3           2006
    Tr4           2006         
    Tr5           2006
    Tr6           2006
    Tr7           2006        


  Var                year
                                  
    Tr1           2007                                           
    Tr3           2007
    Tr4           2007         
    Tr5           2007
    Tr7           2007 

  Var                year
                                  
    Tr1           2008                                          
    Tr2           2008        
    Tr5           2008
    Tr6           2008
    Tr7           2008    
     

What I want is to extract or determinate just the variables present in all tested years (the common ones) , like this example:

  Var                year
                                  
    Tr1           2006                  
    Tr1           2007                         
    Tr1           2008
    Tr5           2006         
    Tr5           2007
    Tr5           2008
    Tr7           2006 
    Tr7           2007
    Tr7           2008

Im working on thousands of data from 2006 to 2016, and I want to keep just the data present in all the tested years. i WISH my request is enough clear to get the right solution. Thank you for your time and help.  

 


Accepted Solutions
Solution
‎09-05-2017 07:32 AM
Respected Advisor
Posts: 4,925

Re: keep only common observations in the same period (year)

Assuming all your data is in dataset have, this should do:

 

proc sql;
create table want as
select unique
    var, 
    year
from have
group by var
having count(distinct year) = (select count(distinct year) from have)
order by var, year;
quit;
PG

View solution in original post


All Replies
Super User
Posts: 5,509

Re: keep only common observations in the same period (year)

I can't test this right now, but it looks like it should work:

 

proc sql noprint;

create table want as select * from have

groupby var

where count(distinct year) = 3;

quit;

New Contributor
Posts: 4

Re: keep only common observations in the same period (year)

Posted in reply to Astounding

I haven't tested it yet , but I think it's similar to the code given by PG stat , Thank you. 

Super User
Posts: 19,815

Re: keep only common observations in the same period (year)

1. Can you have duplicate entries? ie VAR & TRT are the same

2. Are the periods the same, ie if I have 3 distinct years will the all be the same or could it be 2004-2006 and 2005-2007?

 

New Contributor
Posts: 4

Re: keep only common observations in the same period (year)

1. you mean var and year? var is the variable assiociated to year , it couldn't be the same because year= (2006 until 2016) 

var = different entires. 

2.for each year , there is different var , I want to know which var existent in all the period from 2006 to 2016 .For Example :  2006 could contain Tr1 Tr2 Tr3 , and 2007 contains Tr1 Tr2 , so I want to have a table showing me That Tr1 and Tr2 are existent in 2006 and 2007. 

Solution
‎09-05-2017 07:32 AM
Respected Advisor
Posts: 4,925

Re: keep only common observations in the same period (year)

Assuming all your data is in dataset have, this should do:

 

proc sql;
create table want as
select unique
    var, 
    year
from have
group by var
having count(distinct year) = (select count(distinct year) from have)
order by var, year;
quit;
PG
New Contributor
Posts: 4

Re: keep only common observations in the same period (year)

it works! Thank you 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 108 views
  • 1 like
  • 4 in conversation