topic Re: A macro to check whether a variable is blank or not in the dataset in SAS Programming

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

niteshbharadwaj — Fri, 21 Dec 2018 05:37:15 GMT

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

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

andreas_lds — Fri, 21 Dec 2018 06:16:39 GMT

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.

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

niteshbharadwaj — Fri, 21 Dec 2018 06:16:31 GMT

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

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

andreas_lds — Fri, 21 Dec 2018 06:26:29 GMT

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;

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

learsaas — Fri, 21 Dec 2018 06:38:24 GMT

%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);

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

niteshbharadwaj — Fri, 21 Dec 2018 07:24:46 GMT

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;

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

Thanks & Regards

Nitesh

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

learsaas — Fri, 21 Dec 2018 07:31:20 GMT

up case===>upcase

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

niteshbharadwaj — Fri, 21 Dec 2018 12:16:00 GMT

even after correcting still the error exists.

please help

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

Ksharp — Fri, 21 Dec 2018 13:12:36 GMT

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 ;

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

Tom — Fri, 21 Dec 2018 14:43:42 GMT

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;