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.
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;
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;
I haven't tested it yet , but I think it's similar to the code given by PG stat , Thank you.
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?
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.
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;
it works! Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: