BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nayakn
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
nayakn
Fluorite | Level 6

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

View solution in original post

11 REPLIES 11
Reeza
Super User

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

nayakn
Fluorite | Level 6

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

Patrick
Opal | Level 21

@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;
nayakn
Fluorite | Level 6

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?

 

Reeza
Super User

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

 

 

Patrick
Opal | Level 21

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

nayakn
Fluorite | Level 6

@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;
Patrick
Opal | Level 21

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

nayakn
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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