topic Re: Getting rid of a variable, conditionally. in SAS Programming

Getting rid of a variable, conditionally.

VinnyR — Fri, 22 Feb 2019 13:33:18 GMT

I am trying to struggle to write a code in which I want to delete (get rid of) an entire variable if it contains only "0" or "" . How can I write such a code.

Data have;

do i =1 to 5;

if i=1 then do; a="1"; b="2",;c=""; end;

if i=2 then do; a="3"; b="4";c="0"; end;

if i=3 then do; a="5"; b="6"; c=""; end;

if i=4 then do; a="7"; b="8"; c="0"; end;

if i=5 then do; a="9"; b="10"; c=""; end;

output;

end;

run;

Using this data , i want to get rid of c. Kindly help

Re: Getting rid of a variable, conditionally.

Jagadishkatam — Fri, 22 Feb 2019 12:04:50 GMT

You mean you want to drop the variable from the dataset itself if any one row in variable C has zero.

Re: Getting rid of a variable, conditionally.

Jagadishkatam — Fri, 22 Feb 2019 12:09:08 GMT

You can try something like the below code to drop the variable if a condition, i took the example of sashelp.class where sex='M' then i dropped the variables sex.

please try and let me know if it helps.

data class;
set sashelp.class;
if sex='M' then do;
 drop sex;
end;
run;

Re: Getting rid of a variable, conditionally.

RW9 — Fri, 22 Feb 2019 12:12:22 GMT

Why do you have numbers stored as text? This just makes your problem harder. If they were numeric then you could do:

data have (drop=i);
  do i =1 to 5;
     if i=1 then do; a=1; b=2; c=.; end;
     if i=2 then do; a=3; b=4; c=0; end;
     if i=3 then do; a=5; b=6; c=.; end;
     if i=4 then do; a=7; b=8; c=0; end;
     if i=5 then do; a=9; b=10; c=.; end;
  output;
  end;
run;

proc means data=have noprint;
  var _numeric_;
  output out=inter (drop=_type_ _freq_) sum=;
run;
proc transpose data=inter out=inter2;
  var _numeric_;
run;
proc sql noprint;
  select distinct _name_
  into  :drop_list separated by " "
  from  inter2
  where col1=0;
quit;
data want;
  set have (drop=&drop_list.);
run;

Note, your have had errors.

Re: Getting rid of a variable, conditionally.

Kurt_Bremser — Fri, 22 Feb 2019 12:43:55 GMT

@Jagadishkatam drop is a "declarative" statement that the data step compiler acts upon when the data step is compiled; it can't be executed conditionally.

Try this for verification:

data class;
set sashelp.class;
if sex='X' then do;
 drop sex;
end;
run;

Re: Getting rid of a variable, conditionally.

Jagadishkatam — Fri, 22 Feb 2019 12:51:21 GMT

Thank you @Kurt_Bremser for the correction. I agree with you.

Re: Getting rid of a variable, conditionally.

VinnyR — Fri, 22 Feb 2019 13:32:40 GMT

Thanks RW9, i thought of this method as well. But it is too lengthy. I wanted something short and sweet. LIke one datastep. Is that possible?

Re: Getting rid of a variable, conditionally.

RW9 — Fri, 22 Feb 2019 13:36:27 GMT

Nope, you need to go through the data and find indications of a certain value, then in another step drop those records. You could do it using sql, but it wouldn't be any shorter.

The problem you have is that data is driving structure, which should not happen. Structure should be fixed and used for programming. Data should be be flexible and expandable, but always conform to structure. So step 1 will be identify from the data, step 2 is to drop based on that, the two cannot be in one step.

Re: Getting rid of a variable, conditionally.

ErikLund_Jensen — Sun, 24 Feb 2019 09:59:40 GMT

Hi @VinnyR

I totally agree with @RW9 , dynamic structures have no place in a production flow. But if you really want to get rid of all (unspecified) character variables in a data set with missing values, there is only the hard way. But you could put your program in a macro and store it in your permanent macro library. Here is a short macro to do it, is is intended as example only working on your test data and does not account for different libraries etc.

%macro cleands(dsin,dsout);
    %global vlist;
    proc sql noprint;
        select 'x'||name into :vlist separated by ' '
        from dictionary.columns
        where memname = upcase("&dsin") and type = 'char'
		order by varnum;
    quit;

	data _null_; set &dsin end=eof;
		array all $ _character_;
		retain &vlist;
		array vlist &vlist;
		do ix = 1 to dim(all);
			if all{ix} > '0' then vlist{ix} = 1;
		end;
		if eof then do;
			call execute("data &dsout;");
			call execute("set &dsin(drop=");
			do iy = 1 to dim(all);
				if vlist{iy} = '' then call execute( substr(vname(vlist{iy}),2));
			end;
			call execute(");");
			call execute("run;");
		end;
	run;
%mend;

Then you don't even need a data step, just call the macro:

%cleands(have,want);

Nice and short, isn't it? - but it would still be better to use a keep list.

Re: Getting rid of a variable, conditionally.

ErikLund_Jensen — Fri, 22 Feb 2019 16:11:26 GMT

I forgot to delete the put statement in first do loop before posting...

Re: Getting rid of a variable, conditionally.

Ksharp — Sat, 23 Feb 2019 11:59:07 GMT

 

Data have;
   do i =1 to 5;
   if i=1 then do; a="1"; b="2";c=""; end;
   if i=2 then do; a="3"; b="4";c="0"; end;
   if i=3 then do; a="5"; b="6"; c=""; end;
   if i=4 then do; a="7"; b="8"; c="0"; end;
   if i=5 then do; a="9"; b="10"; c=""; end;
   output;
   end;
run;
proc sql;
create table temp as
 select sum(a not in (' ' '0')) as a,
 sum(b not in (' ' '0')) as b,
 sum(c not in (' ' '0')) as c
 from have;
quit;
proc transpose data=temp out=temp1;
run;
proc sql;
select _name_ into : list separated by ',' from temp1
 where col1=0;

alter table have
drop &list;
quit;

Re: Getting rid of a variable, conditionally.

Ksharp — Sat, 23 Feb 2019 11:59:29 GMT

 

Data have;
   do i =1 to 5;
   if i=1 then do; a="1"; b="2";c=""; end;
   if i=2 then do; a="3"; b="4";c="0"; end;
   if i=3 then do; a="5"; b="6"; c=""; end;
   if i=4 then do; a="7"; b="8"; c="0"; end;
   if i=5 then do; a="9"; b="10"; c=""; end;
   output;
   end;
run;
proc sql;
create table temp as
 select sum(a not in (' ' '0')) as a,
 sum(b not in (' ' '0')) as b,
 sum(c not in (' ' '0')) as c
 from have;
quit;
proc transpose data=temp out=temp1;
run;
proc sql;
select _name_ into : list separated by ',' from temp1
 where col1=0;

alter table have
drop &list;
quit;

Re: Getting rid of a variable, conditionally.

ErikLund_Jensen — Sat, 23 Feb 2019 18:19:30 GMT

I find it a daring business to make in situ changes to data. I always try to discourage people from doing that, because it is irreversible. It might be difficult to get a fresh copy if the code fails and input data are lost, and even if it works it is impossible to prove that it worked as intended. So I think it should be one of the "best practice" rules to keep original data for documentational purposes.

Re: Getting rid of a variable, conditionally.

Tom — Sat, 23 Feb 2019 21:26:45 GMT

@ErikLund_Jensen wrote:
I forgot to delete the put statement in first do loop before posting...

Then why not just edit the posting and fix it?

Re: Getting rid of a variable, conditionally.

ErikLund_Jensen — Sun, 24 Feb 2019 10:04:47 GMT

Sorry, I never considered that a possibility. It is done now, but I didn't find a way to delete my correction post, that would be nice too.

Re: Getting rid of a variable, conditionally.

Satish_Parida — Mon, 25 Feb 2019 08:06:06 GMT

Please find the code.

Data have;
   do i =1 to 5;
   if i=1 then do; a="1"; b="2"; c=""; end;
   if i=2 then do; a="3"; b="4"; c="0"; end;
   if i=3 then do; a="5"; b="6"; c=""; end;
   if i=4 then do; a="7"; b="8"; c="0"; end;
   if i=5 then do; a="9"; b="10"; c=""; end;
   output;
   end;
run;

%macro drop_empty_var(ds_in=,ds_out=);
	data _null_;
	set &ds_in.;
	array my_nums[*] _numeric_;
	array my_chars[*] _character_;
	call symput ('dim_n',dim(my_nums));
	call symput ('dim_c',dim(my_chars));
	stop;
	run;

	data _null_;
	set &ds_in. end=last;
	length drop_numvars $2000 drop_charvars $2000;
	array num_delete[&dim_n.];
	array char_delete[&dim_c.];
	array my_nums[*] _numeric_;
	array my_chars[*] _character_;
	retain num_delete char_delete;
	do j=1 to &dim_n.;
		if _n_=1 or num_delete[j] =0 then do;
			/*Condition to delete numeric Variable*/
			if my_nums[j]=. or my_nums[j]=0 then num_delete[j] =0;
			else num_delete[j] =1;
		end; 
	end;
	do j=1 to &dim_c.;
		if _n_=1 or char_delete[j] =0 then do;
			/*Condition to delete charecter Variable*/
			if strip(my_chars[j])='.' or strip(my_chars[j])='' or strip(my_chars[j])='0' then char_delete[j] =0;
			else char_delete[j] =1;
		end; 
	end;

	if last then do;
		do j=1 to &dim_n.;
			if num_delete[j]=0 then do;
				drop_numvars=cats(drop_numvars,',',vname(my_nums[j]));
			end;
		end;
		do j=1 to &dim_c.;
			if char_delete[j]=0 then do;
				drop_charvars=cats(drop_charvars,',',vname(my_chars[j]));
			end;
		end;
		call symput('drop_numvars',translate(drop_numvars,' ',','));
		call symput('drop_charvars',translate(drop_charvars,' ',','));
	end;
	run;

	data &ds_out.(drop=&drop_numvars. &drop_charvars.);
	set &ds_in.;
	run;
%mend drop_empty_var;
%drop_empty_var(ds_in=have,ds_out=want);

It has one additional data step before dropping the variables.

Please let us know it it worked for you.