Keeping only variables with 2 distinct values

[Dritan007]

Hello guys.

I have a huge dataset with 700'000 rows and around 300 variables. 

I wanted to create a new dataset WANT where I keep only variables with 2 types of values.

For example AGE has values M and F.

I am doing this to convert to 0 and 1 and interpret afterward.

Take the example below

data GIVEN;

input ID AGE NAME $ YEAR ACTIVITY $ HEIGHT $;

DATALINES;

1 23 ERI 1995 ACTIVE TALL

2 22 ANDI 1997 ACTIVE SHORT

3 20 ENCA 2000 PASSIVE TALL

4 26 BORA 2001 ACTIVE SHORT

;

RUN;

So here, I want to keep only variables ACTIVITY and HEIGHT since the result is either tall or short, either active or passive.

I have hundreds of variables and I can't seem to work it out.

Thanks in advance

[Tom]

The NLEVELS option on PROC FREQ should help.

ods output nlevels=nlevels;
proc freq data=given nlevels;
  tables _all_ / noprint;
run;

Then it is simple to take the names where NLEVELS=2.  For example you could put the list into a macro variable.

%let binary=;
proc sql noprint;
  select TableVar into :binary separated by ' ' 
  from nlevels
  where nlevels=2
  ;
quit;

1570  %put &=binary ;
BINARY=ACTIVITY HEIGHT

[FreelanceReinh · Accepted solution]

Hello @Dritan007,

I see Tom was faster, but see a few additional suggestions below.

Use the NLEVELS option of PROC FREQ:

ods select none;
ods output nlevels=nlev;
proc freq data=given nlevels;
*tables _character_;
run;
ods select all;

proc sql noprint;
select tablevar into: dichovars separated by ' '
from nlev
where nlevels=2; /* or perhaps: where nnonmisslevels=2; */
quit;

data want;
set given(keep=id &dichovars);
run;

The TABLES statement (commented out) might be useful to obtain separate lists of numeric and character variables (needed for array definitions). The numbers in the NLEVELS variable in dataset NLEV include missing values in the counts, i.e., a variable with one missing value and value 0 otherwise would satisfy the WHERE condition, but not the alternative condition in the comment.