Keeping, dropping, renaming variables without knowing their names

Reply
Occasional Contributor
Posts: 6

Keeping, dropping, renaming variables without knowing their names

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?

Trusted Advisor
Posts: 3,214

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

Posted in reply to ChrisCHPT

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?

---->-- ja karman --<-----
Super User
Posts: 5,432

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

Posted in reply to ChrisCHPT

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

Data never sleeps
Super Contributor
Posts: 297

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

Posted in reply to ChrisCHPT

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

Super User
Posts: 11,343

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

Posted in reply to ChrisCHPT

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

Trusted Advisor
Posts: 3,214

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

Posted in reply to ChrisCHPT

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 791 views
  • 3 likes
  • 5 in conversation