topic keep only common observations in the same period (year) in SAS Programming

keep only common observations in the same period (year)

SAHAR17 — Mon, 04 Sep 2017 22:11:12 GMT

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.

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

Astounding — Mon, 04 Sep 2017 22:55:39 GMT

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;

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

Reeza — Tue, 05 Sep 2017 01:10:38 GMT

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?

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

PGStats — Tue, 05 Sep 2017 03:49:51 GMT

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;

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

SAHAR17 — Tue, 05 Sep 2017 10:07:43 GMT

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.

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

SAHAR17 — Tue, 05 Sep 2017 11:33:34 GMT

it works! Thank you

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

SAHAR17 — Tue, 05 Sep 2017 11:35:53 GMT

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