BookmarkSubscribeRSS Feed
ChrisCHPT
Calcite | Level 5

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?

5 REPLIES 5
jakarman
Barite | Level 11

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 --<-----
LinusH
Tourmaline | Level 20

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

Data never sleeps
Scott_Mitchell
Quartz | Level 8

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

ballardw
Super User

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

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2350 views
  • 3 likes
  • 5 in conversation