A macro to check whether a variable is blank or not in the dataset

[niteshbharadwaj]

Hi Team,

could you please help me knowing how to write a macro which reads a data set and corresponding variable and checking whether variable is blank or not.

for example;

 %macro varcheck (dataset=,varaiable=)

%mend

[andreas_lds]

Please post example data and what you expect as result. Starting without any macro-code is highly recommended, as the task does not seems to require any macro-code, but this, of course, depends on what you want as result.

[niteshbharadwaj]

This is my macro looks 

%macro varexist(ds=,var=);
%local dsid rc ;
%let dsid = %sysfunc(open(&ds));
%if (&dsid) %then %do;
%if %sysfunc(varnum(&dsid,&var)) %then 1;
%else 0 ;
%let rc = %sysfunc(close(&dsid));
%end;
%else 0;
%mend varexist;

but here i want to check whether varaiable has null values or not instead of existing 

[andreas_lds]

Still not clear what you want as result. Checking if a variable exists is NOT the same as checking whether a variable has missing-values. So, again, what do you want?

This example writes the number of missing values found to the log:

/* Create data to demonstrate the checking-step */
data work.class;
   set sashelp.class;

   if Age = 11 then Age = .;
run;


data _null_;
   set work.class end=jobDone;

   length missCount 8;

   missCount + missing(Age);

   if jobDone then do;
      put 'NOTE: Variable AGE is missing in ' missCount 'observations.';
   end;
run;

[learsaas]

%macro varcheck(dataset=sashelp.class,varaiable=age);
	%local nCount nMin nMax;
	proc contents data=&dataset out=temp(keep=name type) noprint;
	run;
	proc sql noprint;
		select count(*) into :nCount
		from temp
		where upcase(name)="%upcase(&varaiable)";
	quit;
	%if %left(&nCount) eq 0 %then %put *************NOT EXIST!**************; 
	%else %do;
		options missing='';
		proc sql noprint;
			select min(&varaiable),max(&varaiable) into :nMin,:nMax 
			from &dataset 
			;
		quit;
		%if "%left(&nMin)" eq "" and "%left(&nMax)" eq "" %then %put *************IS MISSING!**************; 	
	%end;
%mend;
%varcheck(dataset=sashelp.class,varaiable=age);

[niteshbharadwaj]

Hi ,

My problem almost got solved but while implementing the macro these are the below errors am receiving

6 proc contents data=&ds out=&temp(keep=name type) noprint; run; proc sql noprint; select count(*) into :nCount from &temp. where up case(name)="%upcase(&var)"; quit;

_

22

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, CONNECTION, DICTIONARY.

Thanks & Regards

Nitesh

[learsaas]

up case===>upcase

[niteshbharadwaj]

even after correcting still the error exists.

please help

[Ksharp]

ods select none;
ods output nlevels=want;
proc freq data=have nlevels;
table _all_;
run;
ods select all;
proc sql noprint;
select TableVar into : missing_variables separated by ' '
from want
where NNonMissLevels=0;
quit;
%put Missing variables are: &missing_variables ;

[Tom]

Please explain what you are trying to test for.  Since a variable can have a different value on each observation in a dataset what do you mean by a blank variable?  Do you want to know if it ever is blank? Or if it always is blank?

What do you mean by blank?  Are you only testing character variables?  What about numeric variables?  Do you want to test for missing values?  What about special missing values?

If your variables do not have too many levels then a quick way to test if there are any missing (and any non-missing) values is to use the NLEVELS option on PROC FREQ.  

proc freq data=have nlevels ;
  tables varname / noprint;
run;