BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Suki99
Fluorite | Level 6

hi all

I wanted to search across all my clinical datasets that have a certain string in  variable , ie i wanted to find out where that string 'LYNCH' was,

unfortunately in the process ive got a lot of variables that are redundant  ie have missing values, that i dont need and want to get rid of them in the final 'WANT ' dataset

, however i dont know of a quick  and efficient way to tell sas to delete/remove all variables that have missing values across all observations 

 

does anyone know how to do that please 

be indebted .

 

/* find string / value across multiple datasets*/

 

proc sort data=sashelp.vcolumn   out=test   (keep= libname memname) nodupkey;

by libname memname;

where LIBNAME = 'SDTM'   ;

run;

 

proc sql noprint;

 select 'SDTM.'||MEMNAME into :dslist separated by ' '

  from test;

quit;

%put &dslist;

 

 

data WANT /*(where=(chvars{i}))*/;

  set &dslist.     ;

  array chvars{*] _character_ ;

   do i = 1 to dim(chvars);

    if index(upcase(chvars[i]),'LYNCH') then output;

    end;

run;

 

regards

Suki

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

 

 

Well here's the %dropmiss macro that will likely work but as Tom indicates this is a bit of an abnormal request.  

Source: https://support.sas.com/resources/papers/proceedings10/048-2010.pdf

Reference: https://stackoverflow.com/questions/28984570/dropmiss-sas

 

Spoiler
/******************/
options nomprint noSYMBOLGEN MLOGIC;
/****************************/
%macro DROPMISS( DSNIN /* name of input SAS dataset
    */
    , DSNOUT /* name of output SAS dataset
    */
    , NODROP= /* [optional] variables to be omitted from dropping even if
    they have only missing values */
    ) ;
    /* PURPOSE: To find both Character and Numeric the variables that have only
    missing values and drop them if
    * they are not in &NONDROP
    *
    * NOTE: if there are no variables in the dataset, produce no variables
    processing code
    *
    *
    * EXAMPLE OF USE:
    * %DROPMISS( DSNIN, DSNOUT )
    * %DROPMISS( DSNIN, DSNOUT, NODROP=A B C D--H X1-X100 )
    * %DROPMISS( DSNIN, DSNOUT, NODROP=_numeric_ )
    * %DROPMISS( DSNIN, DSNOUT, NOdrop=_character_ )
    */
    %local I ;
    %if "&DSNIN" = "&DSNOUT"
    %then %do ;
    %put /------------------------------------------------\ ;
    %put | ERROR from DROPMISS: | ;
    %put | Input Dataset has same name as Output Dataset. | ;
    %put | Execution terminating forthwith. | ;
    %put \------------------------------------------------/ ;
    %goto L9999 ;
    %end ;
    /*###################################################################*/
    /* begin executable code
    /*####################################################################/
    /*===================================================================*/
    /* Create dataset of variable names that have only missing values
    /* exclude from the computation all names in &NODROP
    /*===================================================================*/
    proc contents data=&DSNIN( drop=&NODROP ) memtype=data noprint out=_cntnts_( keep=
    name type ) ; run ;
    %let N_CHAR = 0 ;
    %let N_NUM = 0 ;
    data _null_ ;
    set _cntnts_ end=lastobs nobs=nobs ;

    if nobs = 0 then stop ;
    n_char + ( type = 2 ) ;
    n_num + ( type = 1 ) ;
    /* create macro vars containing final # of char, numeric variables */
    if lastobs
    then do ;
    call symput( 'N_CHAR', left( put( n_char, 5. ))) ;
    call symput( 'N_NUM' , left( put( n_num , 5. ))) ;
    end ;
    run ;
    /*===================================================================*/
    /* if there are no variables in dataset, stop further processing
    /*===================================================================*/
    %if %eval( &N_NUM + &N_CHAR ) = 0
    %then %do ;
    %put /----------------------------------\ ;
    %put | ERROR from DROPMISS: | ;
    %put | No variables in dataset. | ;
    %put | Execution terminating forthwith. | ;
    %put \----------------------------------/ ;
    %goto L9999 ;
    %end ;
    /*===================================================================*/
    /* put global macro names into global symbol table for later retrieval
    /*===================================================================*/
    %LET NUM0 =0;
    %LET CHAR0 = 0;
    %IF &N_NUM >0 %THEN %DO;
    %do I = 1 %to &N_NUM ;
    %global NUM&I ;
    %end ;
    %END;
    %if &N_CHAR > 0 %THEN %DO;
    %do I = 1 %to &N_CHAR ;
    %global CHAR&I ;
    %end ;
    %END;
    /*===================================================================*/
    /* create macro vars containing variable names
    /* efficiency note: could compute n_char, n_num here, but must declare macro names
    to be
    global b4 stuffing them
    /*
    /*===================================================================*/
    proc sql noprint ;
    %if &N_CHAR > 0 %then %str( select name into :CHAR1 - :CHAR&N_CHAR from
    _cntnts_ where type = 2 ; ) ;
    %if &N_NUM > 0 %then %str( select name into :NUM1 - :NUM&N_NUM from
    _cntnts_ where type = 1 ; ) ;
    quit ;
    /*===================================================================*/
    /* Determine the variables that are missing

    /*
    /*===================================================================*/
    %IF &N_CHAR > 1 %THEN %DO;
    %let N_CHAR_1 = %EVAL(&N_CHAR - 1);
    %END;
    Proc sql ;
    select %do I= 1 %to &N_NUM; max (&&NUM&I) , %end; %IF &N_CHAR > 1 %THEN %DO;
    %do I= 1 %to &N_CHAR_1; max(&&CHAR&I), %END; %end; MAX(&&CHAR&N_CHAR)
    into
    %do I= 1 %to &N_NUM; :NUMMAX&I , %END; %IF &N_CHAR > 1 %THEN %DO;
    %do I= 1 %to &N_CHAR_1; :CHARMAX&I,%END; %END; :CHARMAX&N_CHAR
    from &DSNIN;
    quit;
    /*===================================================================*/
    /* initialize DROP_NUM, DROP_CHAR global macro vars
    /*===================================================================*/
    %let DROP_NUM = ;
    %let DROP_CHAR = ;
    %if &N_NUM > 0 %THEN %DO;
    DATA _NULL_;
    %do I = 1 %to &N_NUM ;
    %IF &&NUMMAX&I =. %THEN %DO;
    %let DROP_NUM = &DROP_NUM %qtrim( &&NUM&I ) ;
    %END;
    %end ;
    RUN;
    %END;
    %IF &N_CHAR > 0 %THEN %DO;
    DATA _NULL_;
    %do I = 1 %to &N_CHAR ;
    %IF "%qtrim(&&CHARMAX&I)" eq "" %THEN %DO;
    %let DROP_CHAR = &DROP_CHAR %qtrim( &&CHAR&I ) ;
    %END;
    %end ;
    RUN;
    %END;
    /*===================================================================*/
    /* Create output dataset
    /*===================================================================*/
    data &DSNOUT ;
    %if &DROP_CHAR ^= %then %str(DROP &DROP_CHAR ; ) ; /* drop char variables
    that
    have only missing values */
    %if &DROP_NUM ^= %then %str(DROP &DROP_NUM ; ) ; /* drop num variables
    that
    have only missing values */
    set &DSNIN ;
    %if &DROP_CHAR ^= or &DROP_NUM ^= %then %do;

    %put /----------------------------------\ ;
    %put | Variables dropped are &DROP_CHAR &DROP_NUM | ;
    %put \----------------------------------/ ;
    %end;
    %if &DROP_CHAR = and &DROP_NUM = %then %do;
    %put /----------------------------------\ ;
    %put | No variables are dropped |;
    %put \----------------------------------/ ;
    %end;
    run ;
    %L9999:
%mend DROPMISS ;

View solution in original post

6 REPLIES 6
Reeza
Super User

This doesn't quite make sense to me:

 

ie i wanted to find out where that string 'LYNCH' was,

unfortunately in the process ive got a lot of variables that are redundant  ie have missing values

 

 

If a variable is being returned as having the string identified, it cannot be all missing at the same time. 

Do you want to remove observations or variables?

 

Suki99
Fluorite | Level 6

hiya

 

my set statement in the final statement calls in or sets about over   24 datasets, so unwanted vars are coming from there.

so to answer your Q , i want to remove variables that just have missing values , but obviously keep the variable(s) that holds the string i want.

 

Reeza
Super User

 

 

Well here's the %dropmiss macro that will likely work but as Tom indicates this is a bit of an abnormal request.  

Source: https://support.sas.com/resources/papers/proceedings10/048-2010.pdf

Reference: https://stackoverflow.com/questions/28984570/dropmiss-sas

 

Spoiler
/******************/
options nomprint noSYMBOLGEN MLOGIC;
/****************************/
%macro DROPMISS( DSNIN /* name of input SAS dataset
    */
    , DSNOUT /* name of output SAS dataset
    */
    , NODROP= /* [optional] variables to be omitted from dropping even if
    they have only missing values */
    ) ;
    /* PURPOSE: To find both Character and Numeric the variables that have only
    missing values and drop them if
    * they are not in &NONDROP
    *
    * NOTE: if there are no variables in the dataset, produce no variables
    processing code
    *
    *
    * EXAMPLE OF USE:
    * %DROPMISS( DSNIN, DSNOUT )
    * %DROPMISS( DSNIN, DSNOUT, NODROP=A B C D--H X1-X100 )
    * %DROPMISS( DSNIN, DSNOUT, NODROP=_numeric_ )
    * %DROPMISS( DSNIN, DSNOUT, NOdrop=_character_ )
    */
    %local I ;
    %if "&DSNIN" = "&DSNOUT"
    %then %do ;
    %put /------------------------------------------------\ ;
    %put | ERROR from DROPMISS: | ;
    %put | Input Dataset has same name as Output Dataset. | ;
    %put | Execution terminating forthwith. | ;
    %put \------------------------------------------------/ ;
    %goto L9999 ;
    %end ;
    /*###################################################################*/
    /* begin executable code
    /*####################################################################/
    /*===================================================================*/
    /* Create dataset of variable names that have only missing values
    /* exclude from the computation all names in &NODROP
    /*===================================================================*/
    proc contents data=&DSNIN( drop=&NODROP ) memtype=data noprint out=_cntnts_( keep=
    name type ) ; run ;
    %let N_CHAR = 0 ;
    %let N_NUM = 0 ;
    data _null_ ;
    set _cntnts_ end=lastobs nobs=nobs ;

    if nobs = 0 then stop ;
    n_char + ( type = 2 ) ;
    n_num + ( type = 1 ) ;
    /* create macro vars containing final # of char, numeric variables */
    if lastobs
    then do ;
    call symput( 'N_CHAR', left( put( n_char, 5. ))) ;
    call symput( 'N_NUM' , left( put( n_num , 5. ))) ;
    end ;
    run ;
    /*===================================================================*/
    /* if there are no variables in dataset, stop further processing
    /*===================================================================*/
    %if %eval( &N_NUM + &N_CHAR ) = 0
    %then %do ;
    %put /----------------------------------\ ;
    %put | ERROR from DROPMISS: | ;
    %put | No variables in dataset. | ;
    %put | Execution terminating forthwith. | ;
    %put \----------------------------------/ ;
    %goto L9999 ;
    %end ;
    /*===================================================================*/
    /* put global macro names into global symbol table for later retrieval
    /*===================================================================*/
    %LET NUM0 =0;
    %LET CHAR0 = 0;
    %IF &N_NUM >0 %THEN %DO;
    %do I = 1 %to &N_NUM ;
    %global NUM&I ;
    %end ;
    %END;
    %if &N_CHAR > 0 %THEN %DO;
    %do I = 1 %to &N_CHAR ;
    %global CHAR&I ;
    %end ;
    %END;
    /*===================================================================*/
    /* create macro vars containing variable names
    /* efficiency note: could compute n_char, n_num here, but must declare macro names
    to be
    global b4 stuffing them
    /*
    /*===================================================================*/
    proc sql noprint ;
    %if &N_CHAR > 0 %then %str( select name into :CHAR1 - :CHAR&N_CHAR from
    _cntnts_ where type = 2 ; ) ;
    %if &N_NUM > 0 %then %str( select name into :NUM1 - :NUM&N_NUM from
    _cntnts_ where type = 1 ; ) ;
    quit ;
    /*===================================================================*/
    /* Determine the variables that are missing

    /*
    /*===================================================================*/
    %IF &N_CHAR > 1 %THEN %DO;
    %let N_CHAR_1 = %EVAL(&N_CHAR - 1);
    %END;
    Proc sql ;
    select %do I= 1 %to &N_NUM; max (&&NUM&I) , %end; %IF &N_CHAR > 1 %THEN %DO;
    %do I= 1 %to &N_CHAR_1; max(&&CHAR&I), %END; %end; MAX(&&CHAR&N_CHAR)
    into
    %do I= 1 %to &N_NUM; :NUMMAX&I , %END; %IF &N_CHAR > 1 %THEN %DO;
    %do I= 1 %to &N_CHAR_1; :CHARMAX&I,%END; %END; :CHARMAX&N_CHAR
    from &DSNIN;
    quit;
    /*===================================================================*/
    /* initialize DROP_NUM, DROP_CHAR global macro vars
    /*===================================================================*/
    %let DROP_NUM = ;
    %let DROP_CHAR = ;
    %if &N_NUM > 0 %THEN %DO;
    DATA _NULL_;
    %do I = 1 %to &N_NUM ;
    %IF &&NUMMAX&I =. %THEN %DO;
    %let DROP_NUM = &DROP_NUM %qtrim( &&NUM&I ) ;
    %END;
    %end ;
    RUN;
    %END;
    %IF &N_CHAR > 0 %THEN %DO;
    DATA _NULL_;
    %do I = 1 %to &N_CHAR ;
    %IF "%qtrim(&&CHARMAX&I)" eq "" %THEN %DO;
    %let DROP_CHAR = &DROP_CHAR %qtrim( &&CHAR&I ) ;
    %END;
    %end ;
    RUN;
    %END;
    /*===================================================================*/
    /* Create output dataset
    /*===================================================================*/
    data &DSNOUT ;
    %if &DROP_CHAR ^= %then %str(DROP &DROP_CHAR ; ) ; /* drop char variables
    that
    have only missing values */
    %if &DROP_NUM ^= %then %str(DROP &DROP_NUM ; ) ; /* drop num variables
    that
    have only missing values */
    set &DSNIN ;
    %if &DROP_CHAR ^= or &DROP_NUM ^= %then %do;

    %put /----------------------------------\ ;
    %put | Variables dropped are &DROP_CHAR &DROP_NUM | ;
    %put \----------------------------------/ ;
    %end;
    %if &DROP_CHAR = and &DROP_NUM = %then %do;
    %put /----------------------------------\ ;
    %put | No variables are dropped |;
    %put \----------------------------------/ ;
    %end;
    run ;
    %L9999:
%mend DROPMISS ;
Suki99
Fluorite | Level 6

many thanks 

this solves my query

 

all the best😊

Tom
Super User Tom
Super User

So you only want CHARACTER variables?

What is the OUTPUT that you want?  Setting together all of the datasets together like that does not make much sense to me.  Why would you stack a DEMOG datasets with an AE dataset and a CONMED dataset?  That is going to create a strange checkboard pattern of missing values since the different datasets will be contributing different variables.

 

And what do you mean by MISSING?  Do you mean variables that have no values at all in the source dataset?  Or variables that have no values when the dataset is subset to just the observations that have LYNCH somewhere?

 

 

 

Ksharp
Super User

https://communities.sas.com/t5/SAS-Programming/Delete-variables-with-90-missing-values/m-p/859816


%macro drop_vars(dsn=,pct=);
proc transpose data=&dsn.(obs=0) out=vname;
var _all_;
run;
data _null_;
set vname end=last;
if _n_=1 then call execute('proc sql;create table n_miss as select ');
call execute(catx(' ','nmiss(',_name_,') as ',_name_));
if last then call execute("from &dsn.;quit;");
else call execute(',');
run;
proc transpose data=n_miss out=n_miss2;
var _all_;
run;
proc sql noprint;
select count(*) into :nobs from &dsn.;
select _NAME_ into :drops separated by ',' from n_miss2 where col1/&nobs. >= &pct. ;
alter table &dsn. drop &drops.;
quit;
%mend;

data have;
set sashelp.heart;
run;
/*
dsn is the dataset you want modify
pct is the percent of missing you want to drop
*/
%drop_vars(dsn=have,pct=1)

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2109 views
  • 2 likes
  • 4 in conversation