I need to check whether the variable is available if yes means I need to save that dataset in output

[_el_doredo]

Hello Experts,

I am having a database which contains more than 50 datasets. I need to check in which dataset a specific variable is available and also in a new dataset I need to save the result as 'yes' or 'no'.

Please guide me on below query

1. I wrote a code. But, I am seeing an empty dataset. I want results dataset as dataset name and Yes or no value in variables_exists variable.
2. If I want to search multiple variable means how I need to tweak my code

Code:

PROC SQL;
SELECT MEMNAME INTO :DATASET_LIST SEPARATED BY ' '
FROM SASHELP.VTABLE
WHERE LIBNAME='STPSAMP';
QUIT;

DATA RESULTS;
LENGTH DATASET $32. VARIABLE_EXISTS $3.;
STOP;
RUN;

%MACRO CHECK_VARIABLE(VAR_NAME);

%LET DS_COUNT=%SYSFUNC(COUNTW(&DATASET_LIST.));
%DO i=1 %TO &DS_COUNT.;
%LET DATASET=%SCAN(&DATASET_LIST.,&i.);

PROC SQL;
SELECT MEMNAME,COUNT(*) INTO : DT_NAME, :VAR_EXISTS
FROM DICTIONARY.COLUMNS
WHERE LIBNAME='STPSAMP' AND MEMNAME="&DATASET."
AND UPPER(NAME)="&VAR_NAME.";
QUIT;

DATA _NULL_;
DATASET=&DATASET.;
VARIABLE_EXISTS=IFC("&VAR_EXISTS.">0,'YES','NO');
CALL SYMPUT("DATASET",DATASET);
CALL SYMPUT("VARIABLE_EXISTS",VARIABLE_EXISTS);
RUN;

DATA RESULTS;
SET RESULTS;
DATASET=SYMGET(DATASET);
VARIABLE_EXISTS=SYMGET(VARIABLE_EXISTS);
OUTPUT;
RUN;
%END;

%MEND CHECK_VARIABLE;

%CHECK_VARIABLE(QTR);

Thanks in Advance

[ballardw · Accepted solution]

Did you have code that worked before you started writing that macro?

If by "database" you mean a SAS Library then this would be my take. The example looks for any data set in the SASHELP library (which you should have) looking for a variable named Sex. Important to note: the case of the variable name in the Name variable can be of variable case. So I use UPCASE for a comparison to 'SEX', an equivalent lower case could be used. SAS will return a numeric 1/0 result for any logical comparison. So this selects the largest of all the comparisons of the names for each Memname. 1 indicates it was found, 0 not.

I strongly recommend using numeric 1 for "Yes" or "True" or "Present" and 0 for "No", "False","Absent" or similar dichotomous values. There things that just plain easier with the numeric values than the character.

proc sql;
    create table work.want as
    select memname,max(upcase(name)='SEX') as Variable_exists
    from dictionary.columns
    where LIBNAME='SASHELP'
    group by memname
    ;
quit;

You would have to show an example of how you want your "multiple" , assuming you mean multiple variables, output data set to look.

For example a minor change to my code would give a count of how many variables are in the set:

proc sql;
    create table work.want as
    select memname,sum(upcase(name)in ('SEX' 'AGE') ) as Variable_exists
    from dictionary.columns
    where LIBNAME='SASHELP'
    group by memname
    ;
quit;

An example of that "numeric 1/0 is easier to work with" for many things.