topic Re: How to select several variables containing same value in SAS Programming

How to select several variables containing same value

Liamb — Thu, 16 Mar 2023 20:22:37 GMT

Hello,
I have a table with 7000 variables coded from 0 to 3 and I want to select only those containing value 1.
How to do it? (dataset with array or proc sql)?
Thanks
Best regards

Re: How to select several variables containing same value

Reeza — Thu, 16 Mar 2023 20:24:32 GMT

More details.
Do you want if any variable in a row is 1?
Do the variables have any naming system? Are all the variables side by side in the data set? Are all the variables the same type (num/char)?

Re: How to select several variables containing same value

Liamb — Thu, 16 Mar 2023 21:26:56 GMT

I want to keep any variable that has a row equal to 1. All my variables are
named differently, e.g. K20I, K20R, D21C, C35D,..... They are all numeric

Re: How to select several variables containing same value

Patrick — Thu, 16 Mar 2023 21:47:11 GMT

@Liamb wrote:
I want to keep any variable that has a row equal to 1. All my variables are
named differently, e.g. K20I, K20R, D21C, C35D,..... They are all numeric

To not have to type the names of 7000 variables we need some naming patterns to select them.

Depending on your data we could also try the other way round and exclude variables: Select all variables that are numeric but don't have a certain name/naming pattern.

Please let us know which way round could work for your data.

Once we can generate the list of variables code like below will work.

data want;
  set sashelp.class;
  if whichn(of _numeric_,15)>0 then output;
run;

Above code selects all rows where at least one of the variables in list _numeric_ has a value of 15.

Re: How to select several variables containing same value

Liamb — Thu, 16 Mar 2023 22:12:56 GMT

I want to select all the variables that have an observation equal to 1 or delete the variables that have no observation equal to 1. Here are an example of data in attached

Re: How to select several variables containing same value

ballardw — Thu, 16 Mar 2023 22:20:39 GMT

Suggestion: provide a small example, maybe 10 variables and 10 rows similar to your data.

Then show us the result you want given that example data.

Your requirement " I want to select only those containing value 1" isn't very clear. Variables that contain 1 somewhere in the data set? For every observation in the data set? Only for some observations.

We may ask questions about why you selected something.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

With the amount of words that SQL requires I shudder to think what any of the code that may possibly work would look like with 7000 (!) variables.

Re: How to select several variables containing same value

Quentin — Thu, 16 Mar 2023 22:44:03 GMT

Hi,

Do you mean you want to check each of 7000 variables, and if a variable has the value 3 for any record, you want to keep that variable?

One way to do this is with PROC TRANSPOSE. You can transpose your dataset to make a vertical dataset with one record per value. So it's basically name-value pairs. Once you have that, you can use PROC SQL (or whatever) to make a list of all the variables that have a certain value. Something like:

proc transpose data=sashelp.class out=vert;
  var _numeric_ ;
  by name ;
run ;

proc print data=vert ;
run ;

proc sql ;
  select distinct _name_ into :keeplist
    from vert 
    where col1=14
  ;
quit ;

data want ;
  set sashelp.class(keep=&keeplist) ;
run ;

Re: How to select several variables containing same value

Liamb — Fri, 17 Mar 2023 06:36:09 GMT

I want to check each of 7000 variables, and if a variable has the value 1 for any record, I want to keep that variable

ID	K20_G	V21_I	K22_K	Q23_H	P25_A	E28_A	E29_E	K30_del	I31_R	K32_A
RET1	1	0	0	0	0	0	0	0	0	0
RET2	0	1	0	0	0	0	0	0	.	0
RET3	0	0	0	1	0	0	0	0	0	0
RET4	0	0	0	0	0	1	0	0	0	0
RET5	1	0	0	0	0	0	0	0	0	0
RET6	0	.	0	0	0	0	0	1	0	0
RET7	0	0	0	0	0	.	0	0	0	0
RET8	0	0	0	0	0	0	0	0	0	0
RET9	0	0	0	0	0	0	0	0	0	0
RET10	0	.	0	0	0	0	0	0	0	1

Re: How to select several variables containing same value

Quentin — Fri, 17 Mar 2023 11:21:10 GMT

I forgot separated by “ “ in the SQL step to make the macro variable KeepList a list of variables.

Re: How to select several variables containing same value

Quentin — Fri, 17 Mar 2023 12:21:08 GMT

Hi,

It's more helpful to post data as a DATA step with cards, so that people can easily create your sample dataset.

But as penance for my earlier typo, I read in your sample data and ran it through my corrected code:

data have ;
  input ID : $5. K20_G V21_I K22_K Q23_H P25_A E28_A E29_E K30_del I31_R K32_A ;
  cards ;
RET1  1 0 0 0 0 0 0 0 0 0
RET2  0 1 0 0 0 0 0 0 . 0
RET3  0 0 0 1 0 0 0 0 0 0
RET4  0 0 0 0 0 1 0 0 0 0
RET5  1 0 0 0 0 0 0 0 0 0
RET6  0 . 0 0 0 0 0 1 0 0
RET7  0 0 0 0 0 . 0 0 0 0
RET8  0 0 0 0 0 0 0 0 0 0
RET9  0 0 0 0 0 0 0 0 0 0
RET10 0 . 0 0 0 0 0 0 0 1
;
run ;

proc sort data=have ;
  by id ;
run ;

proc transpose data=have out=vert;
  var _numeric_ ;
  by id ;
run ;

proc sql noprint;
  select distinct _name_ into :keeplist separated by " "
    from vert 
    where col1=1
  ;
quit ;

%put >>&keeplist<< ;

data want ;
  set have(keep=&keeplist) ;
run ;

Re: How to select several variables containing same value

Kurt_Bremser — Fri, 17 Mar 2023 12:40:48 GMT

Maxim 19: Long Beats Wide.

proc transpose data=have out=long;
by id;
var _numeric_;
run;

data want;
set long;
if _n_ = 1
then do;
  declare hash _1 (dataset:"long (where=(col1 = 1))");
  _1.definekey("_name_");
  _1.definedone();
end;
if _1.check() = 0;
run;

The hash is just my tool of choice (no prior sorting needed), you could also do SQL joins or a DATA step MERGE.