topic Re: Dropping of common variables from multiple datasets in SAS Data Management

Dropping of common variables from multiple datasets

nayakn — Mon, 14 Aug 2017 22:12:06 GMT

Hello All,

I have multiple datasets in SAS which have 10 common system variables which I would like to drop in one go.

Is there any easy way to do it?

For example:

Dataset 1 with common system variable A, B, C, D, E, F, G, H, I, J

Dataset 2 with common system variable A, B, C, D, E, F, G, H, I, J

and so on...

Thanks, N

Re: Dropping of common variables from multiple datasets

Reeza — Mon, 14 Aug 2017 22:18:07 GMT

Do you know the list ahead of time or need to determine it first?

Re: Dropping of common variables from multiple datasets

nayakn — Mon, 14 Aug 2017 22:26:10 GMT

I know the list of varibles which needs to be dropped.

Re: Dropping of common variables from multiple datasets

Patrick — Mon, 14 Aug 2017 22:42:06 GMT

@nayakn

Something like below should work.

proc sql;
  alter table dataset_1
    drop A, B, C, D, E, F, G, H, I, J
  ;
  alter table dataset_2
    drop A, B, C, D, E, F, G, H, I, J
  ;
quit;

Re: Dropping of common variables from multiple datasets

nayakn — Mon, 14 Aug 2017 22:51:06 GMT

Thanks for the quick reponse.

I can do it using below method but I have to specify this for each dataset.

Is there a easy and efficient way so that I can read all the datasets in one go and drop the common variables? Can we do it using Macro?

Re: Dropping of common variables from multiple datasets

Reeza — Mon, 14 Aug 2017 23:40:11 GMT

@nayakn wrote:

Is there a easy and efficient way so that I can read all the datasets in one go and drop the common variables? Can we do it using Macro?

Yes and Yes, but you haven't provided enough information.

The working code is above but how do you identify which datasets need to have the variables dropped?

Whatever the rules are you can select the table names from sashelp.vtable and then use a loop to create the necessary statements. The log has an example of how to loop over a list of variables.

Re: Dropping of common variables from multiple datasets

RW9 — Tue, 15 Aug 2017 08:10:35 GMT

Are all the datasets the same structure, i.e. do they all contain the same set of variables/types? If so, the question would be why are they separate (keep same data together to avoid these kinds of excessive coding), e.g.:

data mydata;
  set dataset: (drop=a b c d e f g h);
run;

The above sets all dataset prefixed datasets into one dataset and drops the given variables.

If they are all different datasets, i.e no structural similarities then:

data _null_;
  set sashelp.vtable (where=(libname="MYLIB"));
  call execute(cats('proc sql; alter table mylib.',memname,' drop a,b,c,d,e,f,g,h; quit;'));
run;

This generates one line of code per dataset in mylib libary and removes the given variables.

Re: Dropping of common variables from multiple datasets

Patrick — Tue, 15 Aug 2017 22:03:30 GMT

@nayakn

I feel code and log remains easier to understand and maintain if you only use SAS macro code where you must.

If this is just about repeating statements a few times then I'd go with such an approach.

If you need something dynamic, i.e. for a varying number of SAS files in a library which follow some naming pattern then you could use macro code or may be even better the code @RW9 posted already.

Re: Dropping of common variables from multiple datasets

nayakn — Wed, 16 Aug 2017 10:26:38 GMT

@RW9 Thanks for the response. Strucutre of all datasets are different but all these datasets do have common system generated variables which I have to remove.

I used the below code in my program but it gives an error message, refer to appended sceenshot.

data _null_;
  set sashelp.vtable (where=(libname="SDTM_MAN"));
  call execute(cats('proc sql; alter table SDTM_MAN.',memname,' drop projectid; quit;'));
run;

Re: Dropping of common variables from multiple datasets

Patrick — Thu, 17 Aug 2017 00:04:17 GMT

@nayakn

The log shows you that there is a blank required before the DROP keyword. Use CATX() with a blank as delimiter instead of CATS() and things should work.

Re: Dropping of common variables from multiple datasets

nayakn — Thu, 17 Aug 2017 10:43:52 GMT

@PatrickThanks for response. I was able to get rid of error message.

Re: Dropping of common variables from multiple datasets

RW9 — Fri, 18 Aug 2017 08:42:00 GMT

Was out of office for a couple of days. For future reference, please mark the answer as the correct answer, not your response to it. This both rewards the person who put the effort in to give you the answer and puts that correct answer up underneath the question so it is easy to pair the two.