DATA Step, Macro, Functions and more

proc sql: WHERE statement multiple columns

Reply
Occasional Contributor
Posts: 15

proc sql: WHERE statement multiple columns

proc sql;
create table combined as
select lifestyle_a, lifestyle_f, lifestyle_g, lifestyle_h, lifestyle_i, lifestyle_j, lifestyle_k, lifestyle_l,
lifestyle_m, lifestyle_n, lifestyle_o, lifestyle_p, lifestyle_q, lifestyle_r, lifestyle_s, lifestyle_u, lifestyle_v, lifestyle_y, lifestyle_aa, lifestyle_ab, lifestyle_ac
from plot1
where lifestyle_a, lifestyle_f, lifestyle_g, lifestyle_h, lifestyle_i, lifestyle_j, lifestyle_k, lifestyle_l, 
lifestyle_m, lifestyle_n, lifestyle_o, lifestyle_p, lifestyle_q, lifestyle_r, lifestyle_s, lifestyle_u, lifestyle_v, lifestyle_y, lifestyle_aa, lifestyle_ab, lifestyle_ac IN (1, 2, 3,4);
quit;

 

I want a table where all the columns have just there four values. I would appreciate community help here,

Super User
Posts: 24,000

Re: proc sql: WHERE statement multiple columns

This would probably be much easier in a data step, use an array and WHICHC. But, if the values are numeric and you want them between 1 and 4 required, you could calculate the max/min and as long as those are between 1 and 4 I think it works the same. 

 

If you post data we can test that approach. 

Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

Thank you for your email. Please find attached the data set. 

Thanks,

 

Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

And I format them by following value

proc format;

Value NegFeel
0 = 'Never'
1 = 'Less than Monthly'
2 = 'Monthly'
3 = 'Weekly'
4 = 'Daily or almost daily'
;
run;

PROC Star
Posts: 629

Re: proc sql: WHERE statement multiple columns

Hi,

 

You can use Dictionary.Columns and structure your where condition and then put that in a macro. See code below

 

Data have;
infile datalines;
input lifestyle_a lifestyle_f lifestyle_g lifestyle_h lifestyle_i lifestyle_j lifestyle_k lifestyle_l;
datalines;
1 2 3 4 1 2 3 4
3 4 5 6 7 1 3 5
;
run;

proc sql;
select name||" IN (1,2,3,4)" INTO: Condition SEPARATED BY " AND "
from dictionary.columns
where libname="WORK" and memname="HAVE" and upcase(name) like "LIFESTYLE_%";
quit;

PROC SQL;
CREATE TABLE NEED AS
SELECT * FROM HAVE
WHERE &Condition.;
quit;

Thanks,
Suryakiran
Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

Posted in reply to SuryaKiran

I run this command but by doing this I am losing 1 from second row  for  lifestyle_j  variable. When I printed need dataset it gave me just one row. It should have given me that value for  lifestyle_j  variable. 

Super User
Posts: 24,000

Re: proc sql: WHERE statement multiple columns


Pooja2 wrote:

I run this command but by doing this I am losing 1 from second row  for  lifestyle_j  variable. When I printed need dataset it gave me just one row. It should have given me that value for  lifestyle_j  variable. 


Ok. Then it's unclear what you're trying to do here. 

Can you work off the example data @SuryaKiran posted and show what output you'd expect from that input?

Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

Okay. For example if I have following dataset. I want to create a data set where I have all the cells where these columns have values in 1 to 4. Hope that is clear.

 

Data have;
infile datalines;
input lifestyle_a lifestyle_f lifestyle_g lifestyle_h lifestyle_i lifestyle_j lifestyle_k lifestyle_l;
datalines;
1 2 3 4 1 2 3 4
3 4 5 6 7 1 3 5

2 3 4 2 4 5 6 7 

1 2 3 3 9 8 8 8
;
run;

 

 

Super User
Super User
Posts: 8,276

Re: proc sql: WHERE statement multiple columns


Pooja2 wrote:

Okay. For example if I have following dataset. I want to create a data set where I have all the cells where these columns have values in 1 to 4. Hope that is clear.

 

Data have;
infile datalines;
input lifestyle_a lifestyle_f lifestyle_g lifestyle_h lifestyle_i lifestyle_j lifestyle_k lifestyle_l;
datalines;
1 2 3 4 1 2 3 4
3 4 5 6 7 1 3 5

2 3 4 2 4 5 6 7 

1 2 3 3 9 8 8 8
;
run;

 

 


You still didn't say which of those four observations meet your criteria. The point of examples is to clarify the words.

Assuming what is want is to only find the first observation you could do something like this.

data have;
  input ls1-ls8 ;
datalines;
1 2 3 4 1 2 3 4
3 4 5 6 7 1 3 5
2 3 4 2 4 5 6 7 
1 2 3 3 9 8 8 8
;

proc print data=have ;
 where 4 >= max(ls1,ls2,ls3,ls4,ls5,ls6,ls7,ls8)
   and 1 <= min(ls1,ls2,ls3,ls4,ls5,ls6,ls7,ls8)
   and 8 = n(ls1,ls2,ls3,ls4,ls5,ls6,ls7,ls8)
 ;
run;

One problem with doing this using a WHERE statement is that the WHERE statement does not support variable lists.  It would much easier to type using a IF statement.

data want ;
  set have;
  if  4 >= max(of ls1-ls8)
   and 1 <= min(of ls1-ls8)
   and 8 = n(of ls1-ls8)
 ;
run;
 
Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

Posted in reply to SuryaKiran

Hi Surya,

I run this command but by doing this I am losing 1 from second row  for  lifestyle_j  variable. When I printed need dataset it gave me just one row. It should have given me that value for  lifestyle_j  variable. 

PROC Star
Posts: 629

Re: proc sql: WHERE statement multiple columns

[ Edited ]

Is this the output your looking for?

 

/* Input data */
Data have;
infile datalines;
input lifestyle_a lifestyle_f lifestyle_g lifestyle_h lifestyle_i lifestyle_j lifestyle_k lifestyle_l;
datalines;
1 2 3 4 1 2 3 4
3 4 5 6 7 1 3 5
2 3 4 2 4 5 6 7
1 2 3 3 9 8 8 8
;
run;
/* Method -1 */
proc sql;
select name||" IN (1,2,3,4)" INTO: Condition SEPARATED BY " AND "
from dictionary.columns
where libname="WORK" and memname="HAVE" and upcase(name) like "LIFESTYLE_%";
quit;

PROC SQL;
CREATE TABLE NEED1 AS
SELECT * FROM HAVE
WHERE &Condition.;
quit;

/* Method-2 */

proc sql;
select count(*),name INTO:count ,: Vars SEPARATED BY " "
from dictionary.columns
where libname="WORK" and memname="HAVE" and upcase(name) like "LIFESTYLE_%";
quit;


DATA need2(drop=i);
set have;
array Var_Check {&count.} &Vars.;
do i = 1 to dim(Var_Check);
IF Var_Check{i} in (1,2,3,4);
end;
run;

Thanks,
Suryakiran
Occasional Contributor
Posts: 15

Re: proc sql: WHERE statement multiple columns

Posted in reply to SuryaKiran

Thanks a lot!

Super User
Super User
Posts: 8,276

Re: proc sql: WHERE statement multiple columns

Posted in reply to SuryaKiran

You don't have to work that hard. SAS can count,

SuryaKiran wrote:

/* Method-2 */

proc sql;
select count(*),name INTO:count ,: Vars SEPARATED BY " "
from dictionary.columns
where libname="WORK" and memname="HAVE" and upcase(name) like "LIFESTYLE_%";
quit;


DATA need2(drop=i);
set have;
array Var_Check {&count.} &Vars.;
do i = 1 to dim(Var_Check);
IF Var_Check{i} in (1,2,3,4);
end;
run;


PROC SQL can count how many records it found.

proc sql noprint;
select name INTO :Vars SEPARATED BY " "
  from dictionary.columns
  where libname="WORK" and memname="HAVE" and upcase(name) like "LIFESTYLE_%"
;
%let count=&sqlobs;
quit;

But you really don't need to calculate &COUNT at all because SAS can also count how many variables you have listed in a array statement.

array Var_Check &Vars.;
Ask a Question
Discussion stats
  • 12 replies
  • 742 views
  • 0 likes
  • 4 in conversation