08-07-2013 03:13 AM
I would like to write some generic code for use in a macro which includes the capability to keep, drop and rename variables, but knowing only the variable's position in the dataset, not its name. For instance, how do I drop the second variable (column) in a dataset without knowing its name?
08-07-2013 03:58 AM
The physical ordering in SAS-data or any table (external table) is not determined.
The position is not really defined, what is your real issue you are trying to solve?
08-07-2013 04:26 AM
You can retrieve the column names (based on their "physical" position) by using ds functions such as open and varname.
08-07-2013 04:58 AM
You could do something like the following.
%LET DSID = %SYSFUNC(OPEN(SASHELP.CLASS,I));
%LET DROP = %SYSFUNC(VARNAME(&DSID,2));
%LET CLOSE = CLOSE(&DSID.);
This produces the name of the second variable (specified by the second argument in the VARNAME statement) in your dataset. I am sure you know how to make this function for multiple variables you wish to drop and how to incorporate it into your existing code.
08-07-2013 05:26 PM
Dictionary.Columns accessible with Proc SQL contains the descriptions of columns in the datasets in your libraries. You could retrieve the varaible name using something like:
select name into: dropname
from dictionary.columns where libname='WORK' and memname='DATASETNAME' and
varnum = <column number value goes here>;
Note that case for libname and memname must be in upper case as that is how the information is stored.
Then use the macro variable value &dropname in any statement you need.
If you want a space delimited list then modify the above
select name into : dropname separated by ' '
and varnum in (<list of column numbers>)
08-08-2013 02:17 AM
I never found a good reason (some exceptions) why the order of variables would be important. See PDV reference.
SAS(R) 9.3 Language Reference: Concepts, Second Edition and http://support.sas.com/resources/papers/proceedings12/255-2012.pdf
Unless some thinking habits are taken from some other tool like Excel, I don not understand this type of questions needing some order position number of a variable when you aree starting to use SAS.