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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.