topic Re: proc sql: WHERE statement multiple columns in SAS Programming

proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 18:36:07 GMT

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,

Re: proc sql: WHERE statement multiple columns

Reeza — Wed, 22 Nov 2017 18:39:20 GMT

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.

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 18:51:15 GMT

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

Thanks,

Re: proc sql: WHERE statement multiple columns

SuryaKiran — Wed, 22 Nov 2017 18:52:05 GMT

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;

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 18:52:28 GMT

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;

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 18:58:34 GMT

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.

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 18:59:43 GMT

Hi Surya,

Re: proc sql: WHERE statement multiple columns

Reeza — Wed, 22 Nov 2017 19:00:18 GMT

@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?

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 19:04:55 GMT

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;

Re: proc sql: WHERE statement multiple columns

SuryaKiran — Wed, 22 Nov 2017 19:18:19 GMT

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;

Re: proc sql: WHERE statement multiple columns

Pooja2 — Wed, 22 Nov 2017 19:26:32 GMT

Thanks a lot!

Re: proc sql: WHERE statement multiple columns

Tom — Thu, 23 Nov 2017 01:06:05 GMT

@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;

Re: proc sql: WHERE statement multiple columns

Tom — Thu, 23 Nov 2017 01:13:22 GMT

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.;