topic Re: How to keep IDs available in each year in SAS Programming

How to keep IDs available in each year

Jonate_H — Thu, 22 May 2014 22:21:57 GMT

I have unbalanced panel data as showed below, there are many years and many IDs, and I want to keep those IDs available in each year, how to achieve that?

* I realized that my sample data set is too simple, which is kind of misleading. So I attached real data set below. Again, thank you guys for your help!

Re: How to keep IDs available in each year

Patrick — Thu, 22 May 2014 22:59:52 GMT

If {year, id} also makes up the composite primary key in your real data then some code as below should work:

libname source "C:\test";

proc sql;

create table want as

select *

from source.sample

group by id

having count(id) = (select count(distinct year) from source.sample)

order by year, id

;

quit;

Re: How to keep IDs available in each year

slchen — Fri, 23 May 2014 01:53:55 GMT

proc sql;

select * from sample where id in (select id from sample group by id having count(year)>=3);

quit;

Re: How to keep IDs available in each year

stat_sas — Fri, 23 May 2014 02:03:06 GMT

data want(drop=n);

set data;

by year id;

if first.year then n=1;

else n+1;

if n<4;

run;

Re: How to keep IDs available in each year

Jonate_H — Fri, 23 May 2014 04:35:44 GMT

Thank you Patrick for your reply. The code works well for the simple data I attached. I don't know why it doesn't work for my real data. When I apply the code to real data, the resulting dataset contains no observation. I have attached real sample in my post.

Re: How to keep IDs available in each year

Jonate_H — Fri, 23 May 2014 04:44:49 GMT

Thank you Sichen. I tried your code with real data, the result contains IDs which only have observations in one year but not the other two years, for example, id=31656 (real sample is in my post). Since Patric's code also doesn't work for my data set, now I am wondering if it is my data set's problem..

Re: How to keep IDs available in each year

Patrick — Fri, 23 May 2014 04:48:16 GMT

That's because in our real sample {year, id} is not the primary composite key so condition "having count(id) = (select count(distinct year) from source.sample)" is not true.

Below code should deal with your real sample:

libname source "C:\test";

proc sql noprint;
create table want as
    select *
    from source.realsample
    group by id
    having count(distinct year) = (select count(distinct year) from source.realsample)
    order by year, id
    ;
quit;

Message was edited by: Patrick Matter

Re: How to keep IDs available in each year

Jonate_H — Fri, 23 May 2014 04:50:52 GMT

I realized that my sample dataset is too simple, so I have updated real data set in the post. Thank you, stat@sas!

Re: How to keep IDs available in each year

Jonate_H — Fri, 23 May 2014 05:06:04 GMT

I am not sure how to fix that problem. I tried to reorder the variables in the data set, still no observations in the result. Really appreciate your help.

Re: How to keep IDs available in each year

Patrick — Fri, 23 May 2014 05:16:40 GMT

See above. I've added code which should work with your "realsample" data.

Re: How to keep IDs available in each year

Jonate_H — Fri, 23 May 2014 23:59:16 GMT

That works great! Many thanks, Patrick!