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?
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?
You can retrieve the column names (based on their "physical" position) by using ds functions such as open and varname.
You could do something like the following.
%LET DSID = %SYSFUNC(OPEN(SASHELP.CLASS,I));
%LET DROP = %SYSFUNC(VARNAME(&DSID,2));
%LET CLOSE = CLOSE(&DSID.);
%PUT **********&DROP.;
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.
Regards,
Scott
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:
proc sql;
select name into: dropname
from dictionary.columns where libname='WORK' and memname='DATASETNAME' and
varnum = <column number value goes here>;
quit;
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>)
http://www.lexjansen.com/wuss/2006/data_presentation_and_business_intelligence/DPR-Choate.pdf
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.
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.