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 ;
@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.
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
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.
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 |
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 ;
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.
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: