Getting rid of a variable, conditionally.

[VinnyR]

Hi 

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

[Jagadishkatam]

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

[Jagadishkatam]

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;

[Kurt_Bremser]

@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;

[Jagadishkatam]

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

[RW9]

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.

[VinnyR]

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?

[RW9]

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.

[ErikLund_Jensen]

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.

[ErikLund_Jensen]

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

[Tom]

---

@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?

[ErikLund_Jensen]

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.

[Ksharp]

 

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;

[Ksharp]

 

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;

[ErikLund_Jensen]

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.