SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Dropping of common variables from multiple datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Dropping of common variables from multiple datasets

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


Accepted Solutions
Solution
‎08-17-2017 06:44 AM
Occasional Contributor
Posts: 5

Re: Dropping of common variables from multiple datasets

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

View solution in original post


All Replies
Super User
Posts: 23,235

Re: Dropping of common variables from multiple datasets

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

Occasional Contributor
Posts: 5

Re: Dropping of common variables from multiple datasets

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

Respected Advisor
Posts: 4,665

Re: Dropping of common variables from multiple datasets

@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;
Occasional Contributor
Posts: 5

Re: Dropping of common variables from multiple datasets

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?

 

Super User
Posts: 23,235

Re: Dropping of common variables from multiple datasets


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. 

 

 

Respected Advisor
Posts: 4,665

Re: Dropping of common variables from multiple datasets

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

Super User
Super User
Posts: 9,397

Re: Dropping of common variables from multiple datasets

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.

Occasional Contributor
Posts: 5

Re: Dropping of common variables from multiple datasets

@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;
Respected Advisor
Posts: 4,665

Re: Dropping of common variables from multiple datasets

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

Solution
‎08-17-2017 06:44 AM
Occasional Contributor
Posts: 5

Re: Dropping of common variables from multiple datasets

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

Super User
Super User
Posts: 9,397

Re: Dropping of common variables from multiple datasets

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 449 views
  • 0 likes
  • 4 in conversation