topic Re: Delete all variables from a datasets that have all missing character valus in SAS Programming

Delete all variables from a datasets that have all missing character valus

Suki99 — Mon, 05 Feb 2024 16:44:21 GMT

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

Re: Delete all variables from a datasets that have all missing character valus

Reeza — Mon, 05 Feb 2024 16:56:45 GMT

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?

Re: Delete all variables from a datasets that have all missing character valus

Suki99 — Mon, 05 Feb 2024 17:05:44 GMT

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.

Re: Delete all variables from a datasets that have all missing character valus

Tom — Mon, 05 Feb 2024 18:13:46 GMT

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?

Re: Delete all variables from a datasets that have all missing character valus

Reeza — Mon, 05 Feb 2024 23:56:11 GMT

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

/******************/
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 ;

Re: Delete all variables from a datasets that have all missing character valus

Ksharp — Tue, 06 Feb 2024 06:57:48 GMT

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)

Re: Delete all variables from a datasets that have all missing character valus

Suki99 — Tue, 06 Feb 2024 14:11:20 GMT

many thanks

this solves my query

all the best😊