topic Ignoring empty variables in SAS Programming

Ignoring empty variables

mlogan — Thu, 02 Jun 2016 19:25:27 GMT

Hi All,

I have some variables in my dataset that doesn't have any data at all. can someone tell me how do I ignore them during the import stage or any other data step? Thanks.

PROC IMPORT DATAFILE="C:\DATA\havexlsx"
DBMS=xlsx replace
OUT=Apple.want;
SHEET='sheet1';
RUN;

Re: Ignoring empty variables

ChrisNZ — Thu, 02 Jun 2016 21:42:41 GMT

What do you mean by "ignore"?

Missing values are ignored when calculations, such a avverage, are made.

Or do you want want to remove the column from the data set? This is dangerous as there might be some data when you import refreshed versions of the file later on, and you then end up with different table structures, and with code must deal with columns that may exist or not.

Re: Ignoring empty variables

sbxkoenk — Thu, 02 Jun 2016 21:51:12 GMT

I've used this in the past:

Sample 24612: Delete variables that have only missing values

http://support.sas.com/kb/24/612.html

See "Full Code" tab for code.

Many alternatives to find on the www (but make sure these programs do exactly what you want in all circumstances!).

Koen

Re: Ignoring empty variables

ballardw — Thu, 02 Jun 2016 22:51:47 GMT

Or a very crude:

data Apple.want;

set Apple.want;

drop VarB VarQ VarX;

run;

If you know the names of the variables you don't want put them in place of the VarB VarQ VarX.

Re: Ignoring empty variables

mlogan — Fri, 03 Jun 2016 00:19:08 GMT

I wanted to ignore the columns that completely null

Re: Ignoring empty variables

mlogan — Fri, 03 Jun 2016 00:20:02 GMT

I need a code which will be dynamic

Re: Ignoring empty variables

PGStats — Fri, 03 Jun 2016 02:59:32 GMT

Tedious but simple to do with a _null_ data step. An example with sashelp.cars

data test;
set sashelp.cars;
call missing (Origin, Cylinders); /* Make them all missing */
run;

data _null_;
set test end=done;
array _n _numeric_ _dumn;
array _c _character_ _dumc;
array _nn{2000} _temporary_;
array _nc{2000} _temporary_;
do i = 1 to dim(_n)-1;
    if not _nn{i} and not missing(_n{i}) then _nn{i} = 1;
    end;
do i = 1 to dim(_c)-1;
    if not _nc{i} and not missing(_c{i}) then _nc{i} = 1;
    end;
length _vars $1000;
if done then do;
    do i = 1 to dim(_n)-1;
        if not _nn{i} then _vars = catx(" ", _vars, vname(_n{i}));
        end;
    do i = 1 to dim(_c)-1;
        if not _nc{i} then _vars = catx(" ", _vars, vname(_c{i}));
        end;
    call symputx("droplist", _vars);
    end;
run;

data test2;
_dummy = 0; /* In case the drop list is empty */
set test;
drop &droplist _dummy;
run;

Re: Ignoring empty variables

ChrisNZ — Fri, 03 Jun 2016 03:19:29 GMT

Like this?

data HAVE;
  do i=1 to 12;
    'VARlkj-- *&^*&^,'n = ' ';
    output; 
  end; 
run;
                      
proc sql noprint;

  select NAME into :var_list separated by '\'
  from DICTIONARY.COLUMNS 
  where LIBNAME='WORK' and MEMNAME='HAVE';

  select catx(' ',
         %macro loop_vars;
         %local i ;
         %do i=1 %to %sysfunc(countw(%superq(var_list),\));
         %if &i>1 %then ,;
          missing(max("%scan(%superq(var_list),&i,\)"n)) 
         %end;
         %mend; %loop_vars
         )
         into :missing_flag_list 
         from HAVE
         ;                   

  create table WANT as 
  select *
  from HAVE(drop=
         %macro loop_vars;
         %local i ;
         %do i=1 %to %sysfunc(countw(%superq(var_list),\));
          %if %scan(&missing_flag_list,&i) %then "%scan(%superq(var_list),&i,\)"n  ;
         %end;
         %mend; %loop_vars
        )
  ;

quit;

Re: Ignoring empty variables

RW9 — Fri, 03 Jun 2016 08:34:32 GMT

Why is that? It sounds to me like you don't know your own data then. What does the data transfer agreement say? Does that allow missing columns? Don't have one of those, well that is the problem. Your trying to guess what the data is without knowing the file strcuture or the contents, fix the process (i.e. do it properly in a documented manner using appropriate file formats and processes) and these problems dissappear.

Re: Ignoring empty variables

PGStats — Fri, 03 Jun 2016 15:23:10 GMT

@RW9, I agree with your evaluation of spreadsheets as databases. But not everyone uses SAS within a process. Some are data buyers and consumers, but others are just gleaners. Research and journalism, for example, often have no control over the structure of the data that they gather.

Re: Ignoring empty variables

Tom — Fri, 03 Jun 2016 17:11:05 GMT

It is probably easier to use the NLEVELS option on PROC FREQ than using the older macros that have been around for years.

data test;
 set sashelp.class ;
 no_number=.;
 no_character=' ';
run;

ods exclude nlevels;
ods output nlevels=nlevels;
proc freq nlevels data=test;
 tables _all_ / noprint;
run;

You can then get the list of variable names that are all missing from the NLEVELS dataset.

proc sql noprint;
  select tablevar into :droplist separated by ' '
  from nlevels
  where nnonmisslevels=0
  ;
quit;

Or you could do the reserve and get the list of variables that have non-missing data.

Re: Ignoring empty variables

mlogan — Sat, 04 Jun 2016 02:34:34 GMT

Hi Tom,
Your code doesn't create a data set with the non missing variables. Am I missing something from your code??

Re: Ignoring empty variables

PGStats — Sat, 04 Jun 2016 02:51:55 GMT

Complete @Tom's code with the last step from my program up there.

Re: Ignoring empty variables

Tom — Sat, 04 Jun 2016 03:20:30 GMT

Creating a new dataset without selected variables is just the simple case of writing a data step with a DROP statement in it. The dynamic part is to have a method where the list of variables does not need to be hard coded. That is what the macro variable is for.

data want ;
  set have;
  drop &droplist;
run;