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
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;
@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;
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.
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.
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 wrote:
I forgot to delete the put statement in first do loop before posting...
Then why not just edit the posting and fix it?
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;
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.