topic Re: how to subset observations based on id if they exist for all years.... NEED HELP in SAS Data Science

how to subset observations based on id if they exist for all years.... NEED HELP

Ahmad — Mon, 09 Jul 2012 00:37:34 GMT

hi i have a data set with the following variables stockname price date year

i have data for 7 years for 100+ stocks

i want to subset my data set and keep only those stocks which have been traded in all years,

e.g.

input stockname price date year;

abc 26 20071205 2007

abc 35 20071206 2007

abc 75 20080123 2008

abc 45 20080612 2008

abc 55 20090612 2009

xyz 26 20071205 2007

xyz 35 20071206 2007

xyz 75 20080123 2008

xyz 45 20080612 2008

;

Now in this example stock abc is traded for all three years but xyz is not , i want to subset data to exclude all stocks that are not traded foro the whole time period

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Lea — Mon, 09 Jul 2012 01:49:04 GMT

If you know that the number of years is constant (7), I would use first. and last. logic.

Proc sort data=dataset;

by stockname;

run;

Data limit;

set dataset;

by stockname;

if first.stockname then counter = 0;

counter + 1;

if last.stockname;

if counter lt 7 then delete;

run;

This will give you only the final year for the stocks you want, and you can go back and pick up the other data if you need it.

Does this work?

Lea

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ahmad — Mon, 09 Jul 2012 01:52:31 GMT

thanks for the reply, but you mean to say i will have to pick the other data manually, (e.g if i know the stcoks which have data for 7 years, how would i subset my data for all only those stocks??)

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ahmad — Mon, 09 Jul 2012 01:55:29 GMT

because the number of stocks is several hundred, close to 500, and the frequency is daily

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Lea — Mon, 09 Jul 2012 02:02:04 GMT

If I'm understanding your problem correctly, you can use two steps. First, de-dupe your data by stock and year, and then run the logic above to grab only those stocks traded in all 7 years. Second, you would merge that list of stocks back with your other data (that picks up price and date) to get the final dataset.

Data final;

merge limit (in=in1 keep=stockname) dataset;

by stockname;

if in1;

run;

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ahmad — Mon, 09 Jul 2012 02:17:50 GMT

hey thanks Lea works perfect

Ahmad

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ksharp — Mon, 09 Jul 2012 03:03:09 GMT

Or SQL. If you like it.

data have;
input stockname $ price date year;
cards;
abc 26  20071205 2007
abc 35 20071206 2007
abc 75 20080123 2008
abc 45 20080612 2008
abc 55 20090612 2009
xyz 26  20071205 2007
xyz 35 20071206 2007
xyz 75 20080123 2008
xyz  45 20080612 2008
;
run;
proc sql ;
select count(distinct year) into : n from have;
create table want as
 select * from have 
  where stockname in (
       select stockname from (select stockname,year,count(*) as _count from have group by stockname,year)
         group by stockname
          having count(year) eq &n);
quit;

Ksharp

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Howles — Mon, 09 Jul 2012 13:07:54 GMT

Or maybe

create table want as

select * from have

group by stockname

having count(distinct year)

EQ (select range(year)+1 from have)

order by stockname, date ;

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ksharp — Tue, 10 Jul 2012 09:10:51 GMT

Thanks, Howles.

Which remind me the skill I have forgotten. I am old i think.

proc sql ;
create table want as
 select * from have 
    group by stockname
       having count(distinct year) eq (select count(distinct year) from have);
quit;

Ksharp

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Howles — Tue, 10 Jul 2012 13:52:14 GMT

I would still use RANGE to determine the correct number of years. Consider this test data:

data have;

input stockname $ price date year;

cards;

abc 35 20071206 2007

abc 55 20090612 2009

;

Re: how to subset observations based on id if they exist for all years.... NEED HELP

Ahmad — Sun, 25 Nov 2012 22:42:35 GMT

hi ksharp can you please explain your code a bit, as it only gives one value per stock per year, but what i want is to get daily data for stocks which have data for all seven years. Thanks