topic Re: Selecting with regard to more than one variable in PROC SQL. in SAS Procedures

Selecting with regard to more than one variable in PROC SQL.

deleted_user — Thu, 04 Mar 2010 12:46:09 GMT

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.

Re: Selecting with regard to more than one variable in PROC SQL.

MikeZdeb — Thu, 04 Mar 2010 13:32:06 GMT

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]

Re: Selecting with regard to more than one variable in PROC SQL.

deleted_user — Thu, 04 Mar 2010 14:05:30 GMT

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.

Re: Selecting with regard to more than one variable in PROC SQL.

sbb — Thu, 04 Mar 2010 14:15:11 GMT

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

Re: Selecting with regard to more than one variable in PROC SQL.

MikeZdeb — Thu, 04 Mar 2010 14:21:14 GMT

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]

Re: Selecting with regard to more than one variable in PROC SQL.

deleted_user — Thu, 04 Mar 2010 14:35:45 GMT

Thank you. Nice solution. I should have figured it out by myself.