BookmarkSubscribeRSS Feed
deleted_user
Not applicable
My data set,OldTable, has 6 numeric variables ID (identity number), YEAR, N1-N4.

An identity(ID) can have several records each YEAR.

In one(1) new data set I want, for each year, an individuals all records if the individual has at least one record, for that year, where the value for N1 equals 1.

Ex.(for a single identity)
OldTable
ID_YEAR_ N1_ N2 _N3_ N4
11_2008__0__40__30__90
11_2008__1__30__35__50
11_2008__1__20__25__60
11_2009__0__30__50__95
11_2009__0__40__30__60
11_2010__1__20__50__40
11_2010__0__50__30__50

NewTable
ID_YEAR_ N1_ N2 _N3_ N4
11_2008__0__40__30__90
11_2008__1__30__35__50
11_2008__1__20__25__60
11_2010__1__20__50__40
11_2010__0__50__30__50

I know how to do it, if I didn't have to regard the different years:

select * from oldtable where id in (select distinct id from oldtable where n1=1);

If it's possible I would prefer not to use a macro processing different years.
5 REPLIES 5
MikeZdeb
Rhodochrosite | Level 12
hi ...
[pre]
data old;
input ID YEAR N1-N4;
datalines;
11 2008 0 40 30 90
11 2008 1 30 35 50
11 2008 1 20 25 60
11 2009 0 30 50 95
11 2009 0 40 30 60
11 2010 1 20 50 40
11 2010 0 50 30 50
;
run;

proc sql noprint;
select * from old
where year in (select distinct year from old where n1 eq 1);
quit;

proc print data=new;
run;

Obs ID YEAR N1 N2 N3 N4
1 11 2008 0 40 30 90
2 11 2008 1 30 35 50
3 11 2008 1 20 25 60
4 11 2010 1 20 50 40
5 11 2010 0 50 30 50
[/pre]
deleted_user
Not applicable
That doesn't work. My example was for a single identity.

If there had been an identity 12 in year 2009 with one or several records where N1 would equal 1, then the two records for identity 11 for year 2009, would also have been selected for the new data set.

Both YEAR an ID have significance.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As recommended at your other post, you may consider using macro code to generate unque SAS data files for each ID and YEAR combination encountered based on your input SAS file.

Start with a PROC SQL skeleton program which uses WHERE and selects one ID and YEAR to create a specific-named file. Then once you get that logic to work as desired, look at creating a SAS macro driven by called macro variable parameters -- these would be DISTINCT combination of ID and YEAR.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/forums/thread.jspa?threadID=8835
MikeZdeb
Rhodochrosite | Level 12
hi ... OK ... select on ID and YEAR ...
[pre]
data old;
input ID YEAR N1-N4;
datalines;
11 2008 0 40 30 90
11 2008 1 30 35 50
11 2008 1 20 25 60
11 2009 0 30 50 95
11 2009 0 40 30 60
11 2010 1 20 50 40
11 2010 0 50 30 50
22 2008 0 40 30 90
22 2008 0 30 35 50
22 2008 0 20 25 60
22 2009 1 30 50 95
22 2009 1 40 30 60
22 2010 0 20 50 40
22 2010 0 50 30 50
33 2001 1 40 30 90
33 2001 0 30 35 50
33 2001 0 20 25 60
33 2004 1 30 50 95
33 2004 0 40 30 60
33 2005 1 20 50 40
33 2005 0 50 30 50
;
run;

proc sql noprint;
create table new as
select * from old
where catt(id,year) in (select distinct catt(id,year) from old where n1 eq 1);
quit;

proc print data=new;
by id;
run;

OUTPUT ...
ID=11
Obs YEAR N1 N2 N3 N4
1 2008 0 40 30 90
2 2008 1 30 35 50
3 2008 1 20 25 60
4 2010 1 20 50 40
5 2010 0 50 30 50

ID=22
Obs YEAR N1 N2 N3 N4
6 2009 1 30 50 95
7 2009 1 40 30 60

ID=33
Obs YEAR N1 N2 N3 N4
8 2001 1 40 30 90
9 2001 0 30 35 50
10 2001 0 20 25 60
11 2004 1 30 50 95
12 2004 0 40 30 60
13 2005 1 20 50 40
14 2005 0 50 30 50
[/pre]
deleted_user
Not applicable
Thank you. Nice solution. I should have figured it out by myself.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 744 views
  • 0 likes
  • 3 in conversation