BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAHAR17
Calcite | Level 5

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.  

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
Astounding
PROC Star

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;

SAHAR17
Calcite | Level 5

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

Reeza
Super User

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?

 

SAHAR17
Calcite | Level 5

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. 

PGStats
Opal | Level 21

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
SAHAR17
Calcite | Level 5

it works! Thank you 

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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