topic Re: Check for Column and missing in SAS Programming

Check for Column and missing

SASPhile — Wed, 22 Aug 2018 20:05:54 GMT

there are 4 datasets a,b,c,d.
How to look for a vraible name that has TEST in variablename like ATEST, BTEST and see if there aleast one record misssing?

Table A:
Id ATEST
1 Testa
2 Testa


Table B:
Id BTEST
1 Testb
2

Table C:
Id CTEST
1 TestC
2 TestC

Table 😧
Id DTEST
1
2

So the output would be:

Dataset B    BTEST
Dataset D    DTEST

Re: Check for Column and missing

UdayGuntupalli — Wed, 22 Aug 2018 20:16:46 GMT

@SASPhile,
This may give you an idea on how to proceed

%macro VarExist(ds, var);
    %local rc dsid result;
    %let dsid = %sysfunc(open(&ds));
 
    %if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
        %let result = 1;
        %put NOTE: Var &var exists in &ds;
    %end;
    %else %do;
        %let result = 0;
        %put NOTE: Var &var not exists in &ds;
    %end;
 
    %let rc = %sysfunc(close(&dsid));
    &result
%mend VarExist;
 
/* Usage */
%put %VarExist(sashelp.class, name);
%put %VarExist(sashelp.class, aaa);

Source : http://www.sascommunity.org/wiki/Tips:Check_if_a_variable_exists_in_a_dataset

Re: Check for Column and missing

Reeza — Wed, 22 Aug 2018 20:18:15 GMT

Are you designing this system? Can you change the naming convention so it's TESTA, TESTB, etc?

SAS works better with prefixes rather than suffixes.

Re: Check for Column and missing

SASPhile — Wed, 22 Aug 2018 20:18:54 GMT

I'm not looking if a variable exists. I'm looking for a variable that contains TEST as part of variablename and if it has at least one missing values.

Re: Check for Column and missing

ballardw — Wed, 22 Aug 2018 20:47:18 GMT

@SASPhile wrote:

I'm not looking if a variable exists. I'm looking for a variable that contains TEST as part of variablename and if it has at least one missing values.

HOW many variables are you concerned with in each data set? What does the output potentially look like with 10 variables?

And are you really sure that you want every variable that may have 'TEST' as part of the name? What if you have a variable named "testimony" "intestate"

Do you want each time a variable is missing or if any records in the set are missing values at least once?