topic Re: Keeping, dropping, renaming variables without knowing their names in SAS Programming

Keeping, dropping, renaming variables without knowing their names

ChrisCHPT — Wed, 07 Aug 2013 07:13:30 GMT

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?

Re: Keeping, dropping, renaming variables without knowing their names

jakarman — Wed, 07 Aug 2013 07:58:34 GMT

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?

Re: Keeping, dropping, renaming variables without knowing their names

LinusH — Wed, 07 Aug 2013 08:26:49 GMT

You can retrieve the column names (based on their "physical" position) by using ds functions such as open and varname.

Re: Keeping, dropping, renaming variables without knowing their names

Scott_Mitchell — Wed, 07 Aug 2013 08:58:14 GMT

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

Re: Keeping, dropping, renaming variables without knowing their names

ballardw — Wed, 07 Aug 2013 21:26:59 GMT

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>)

Re: Keeping, dropping, renaming variables without knowing their names

jakarman — Thu, 08 Aug 2013 06:17:29 GMT

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.