BookmarkSubscribeRSS Feed
Pooja2
Fluorite | Level 6

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,

12 REPLIES 12
Reeza
Super User

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. 

Pooja2
Fluorite | Level 6

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

Thanks,

 

Pooja2
Fluorite | Level 6

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;

SuryaKiran
Meteorite | Level 14

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
Pooja2
Fluorite | Level 6

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. 

Reeza
Super User

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

Pooja2
Fluorite | Level 6

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;

 

 

Tom
Super User Tom
Super User

@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;
 
Pooja2
Fluorite | Level 6

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. 

SuryaKiran
Meteorite | Level 14

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
Pooja2
Fluorite | Level 6

Thanks a lot!

Tom
Super User Tom
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 7449 views
  • 0 likes
  • 4 in conversation