Hello all.
I have a simple question for base sas programming.
I want to delete variables if their name meet some condition.
For example
I have variables port1_1, port1_2, port1_3, port2_1, port2_2, port2_3, port3_1, port3_2, port3_3
I want to delete port1_1, port2_2, port3_3
I made a list of variable name to delete in separate dataset, but I don't know how to use them...
What I have is two datasets
have1 and have2
<have1>
family | stock | port1_1 | port1_2 | port1_3 | port1_4 | port2_1 | port2_2 | port2_3 | port2_4 | port3_1 | port3_2 | port3_3 | port3_4 | port4_1 | port4_2 | port4_3 | port4_4 |
1 | 1 | 1 | 0 | 1 | . | 0 | 0 | 0 | . | 1 | 0 | 1 | . | . | . | . | . |
1 | 2 | 1 | 1 | 0 | . | 1 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 3 | 1 | 0 | 1 | . | 0 | 0 | 0 | . | 1 | 0 | 1 | . | . | . | . | . |
1 | 4 | 1 | 1 | 1 | . | 1 | 1 | 1 | . | 1 | 1 | 1 | . | . | . | . | . |
1 | 5 | 0 | 0 | 0 | . | 0 | 1 | 1 | . | 0 | 1 | 1 | . | . | . | . | . |
1 | 6 | 1 | 1 | 1 | . | 1 | 1 | 1 | . | 1 | 1 | 1 | . | . | . | . | . |
1 | 7 | 0 | 0 | 0 | . | 0 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 9 | 0 | 0 | 0 | . | 0 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 11 | 1 | 0 | 0 | . | 0 | 0 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 12 | 1 | 1 | 1 | . | 1 | 1 | 1 | . | 1 | 1 | 1 | . | . | . | . | . |
1 | 13 | 1 | 1 | 1 | . | 1 | 1 | 1 | . | 1 | 1 | 1 | . | . | . | . | . |
1 | 15 | 1 | 1 | 0 | . | 1 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 16 | 0 | 0 | 0 | . | 0 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 17 | 1 | 1 | 0 | . | 1 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 18 | 1 | 1 | 0 | . | 1 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 19 | 0 | 0 | 0 | . | 0 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 21 | 1 | 1 | 0 | . | 1 | 1 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 23 | 1 | 0 | 0 | . | 0 | 0 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
1 | 24 | 1 | 0 | 0 | . | 0 | 0 | 0 | . | 0 | 0 | 0 | . | . | . | . | . |
2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
2 | 2 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 |
2 | 3 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 |
2 | 4 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 |
2 | 5 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 |
2 | 6 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
2 | 7 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
2 | 8 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | 9 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
2 | 11 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
2 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
2 | 27 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 |
2 | 28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 |
2 | 29 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 |
<have2> --> they are the variables that I want to delete
Variable Name |
port1_1 |
port2_2 |
port3_3 |
port4_4 |
how do I efficiently delete them??
Use proc sql to create a list for dropping:
proc sql noprint;
select name into :deletelist separated by ' ' from have2;
quit;
data want;
set have;
drop &deletelist;
run;
With suitable conditions, you can pull such lists directly from dictionary.columns.
@Kurt_Bremser has the right idea. In part, the answer depends on what you mean by "efficient". To get his answer to run a little bit faster, change the final step to:
data want;
set have (drop=&deletelist);
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.