topic Re: why is my drop not working in SAS Programming

why is my drop not working

McA — Wed, 10 Oct 2018 10:59:05 GMT

I Have the following code:

%macro dropds;

%local num i;
%do i=1 %to &num;
data &source..&&ds&i;
set &source..&&ds&i;

PROC CONTENTS NOPRINT DATA=&source..&&ds&i OUT=varout;
run;

/* does current dataset have numeric dates and times then delete */
PROC SQL noprint;
SELECT name into :droplist
FROM varout
where format in ('DATE', 'TIME')
;
QUIT;

drop SUBJECTVISITID VISITID FORMID FORMINDEX VISITINDEX &droplist;
%end;
%mend;

But in all the datasets still remain the variables (SUBJECTVISITID VISITID FORMID FORMINDEX VISITINDEX) that should have been dropped, it seems like I am blind

Re: why is my drop not working

Kurt_Bremser — Wed, 10 Oct 2018 11:06:32 GMT

You never set the macro variable num, so the %do loop never iterates.

Re: why is my drop not working

s_lassen — Wed, 10 Oct 2018 11:31:13 GMT

Apart from the problem mentioned by @Kurt_Bremser, it looks like you also have a problem with the code. You cannot insert PROC CONTENTS and PROC SQL code in the middle of a datastep, the first PROC keyword will signal the end of the datastep code, so the DROP statement is completely disconnected from the data step.

So you should find the variables to drop BEFORE running the data step. And you can just use DICTIONARY.COLUMNS, no need for a PROC CONTENTS, e.g.

proc sql;
  select name into :drop separated by ' '
  from dictionary.columns
  where libname="%upcase(&source)"
    and memname="%upcase(&&ds&i)"
    and (format like 'DATE%' or format like 'TIME%')
  ;
quit;

data &source..&&ds&i;
  set &source..&&ds&i;
  drop &drop;
run;

Re: why is my drop not working

SuryaKiran — Wed, 10 Oct 2018 12:44:20 GMT

I see mainly three issues here:

1) &num is not set anywhere in the code you provided.

2) Your using drop statement as open code.

you have to change that to as @s_lassen mentioned.

data &source..&&ds&i;
set &source..&&ds&i;
drop &drop;
run;

3) Your where clause where format in ('DATE', 'TIME') might not include all the date & time formats that are in you dataset.

example 1:
In below example I actually have date values but no format defined. In this case your where clause might not filter these columns.

data test;
value='20oct2018'd;
run;
PROC CONTENTS NOPRINT DATA=test OUT=varout;
run;

example 2:
Here below the dataset have date values but format is different. In this case you might need to include where format in ('MMDDYY')

data test;
format value mmddyy10.;
value='20oct2018'd;
run;
PROC CONTENTS NOPRINT DATA=test OUT=varout;
run;

Your where clause will not exclude all the date and time variables unless they are defined in the format you specified in where clause. So make sure you have DATE and TIME formats included in source datasets.

You don't need to run PROC CONTENTS and PROC SQL to get the variables, you jsut need PROC SQL query for DICTIONARY.CLOUMNS.

%macro dropds(num=);

%local num i;
%do i=1 %to &num;
data &source..&&ds&i;
set &source..&&ds&i;
run;

/* does current dataset have numeric dates and times then delete */
PROC SQL noprint;
SELECT name into :droplist
FROM dictionary.cloumns
where libname=upcase("&source") and upcase(memname)=upcase("&&ds&i") 
	and format in ('DATE9.', 'TIME5.') /* All Date & Time formats here */
;
QUIT;
data &source..&&ds&i;
set &source..&&ds&i;
drop SUBJECTVISITID VISITID FORMID FORMINDEX VISITINDEX &droplist;
run;
%end;
%mend;

Re: why is my drop not working

ballardw — Wed, 10 Oct 2018 14:31:20 GMT

There are more than 100 SAS supplied date, time or date time formats. Not to mention the possibility of custom formats for date, time or datetime values.

Re: why is my drop not working

McA — Fri, 12 Oct 2018 10:37:31 GMT

I figured out the solution by myself. I just needed a fresh mind. I even had to change the position within the program, that no variables are dropped, which I still need.

%MACRO renam (dsin=,dsout=);
PROC DATASETS LIB=MR NOLIST NOPRINT;
CHANGE &dsin = &dsout;
QUIT;

PROC CONTENTS NOPRINT DATA=&dsout OUT=varout;
run;

PROC SQL noprint;
SELECT name into :droplist
FROM varout
where format in ('DATE', 'TIME');
QUIT;

DATA &DSOUT;
SET &DSIN;
DROP SUBJECTVISITID VISITID FORMID FORMINDEX VISITINDEX &droplist;;
RUN;
%MEND renam;