The SAS Output Delivery System and reporting techniques

Need to find nth Variable from given dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

Need to find nth Variable from given dataset

Hi,

i have query regarding Base SAS. i have dataset suppose test and i don't know how many obeservation it has. so i wanted to display nth Variable only (considering n is the any non-negative value>0) also i don't want to use Proc Content. so how could be possible this? Please let me know. i have same issue with database but i got query for sqlserver 2008 which solves my issue. How can i resolve this issue using SAS when there is dataset given.

the SQL query which i used against sqlserver database is the below one.

--If you want to Fetch the nth column name (only name)

Declare @TableName as nVarchar(100);

Declare @NthCol as Int

Select

          @TableName =N'Sysobjects',

          @NthCol=2

select Col_name(object_id(@TableName),@NthCol) ColumnName

  

Code Snippet

--If you want to select Record for given Column (with data)

Declare @TableName as nVarchar(100);

Declare @NthCol as Int

Select

          @TableName =N'Sysobjects',

          @NthCol=2

Declare @ColName as varchar(100);

select @ColName = Col_name(object_id(@TableName),@NthCol)

Exec ('Select ' + @ColName + ' From ' + @TableName)

Please Help. Thanks in Advance.


Accepted Solutions
Solution
‎10-29-2012 11:27 AM
PROC Star
Posts: 7,473

Re: Need to find nth Variable from given dataset

Will the following work for you?:

%let n=5;

proc sql;

/*  get Nth variable name and show it in log*/

  select name into :vname

    from dictionary.columns

      where libname=upcase("sashelp") and

            memname=upcase("class") and

            varnum=&n.

  ;

  %put &vname.;

/*  output all values for Nth variable*/

  select &vname.

    from sashelp.class

  ;

quit;

View solution in original post


All Replies
Solution
‎10-29-2012 11:27 AM
PROC Star
Posts: 7,473

Re: Need to find nth Variable from given dataset

Will the following work for you?:

%let n=5;

proc sql;

/*  get Nth variable name and show it in log*/

  select name into :vname

    from dictionary.columns

      where libname=upcase("sashelp") and

            memname=upcase("class") and

            varnum=&n.

  ;

  %put &vname.;

/*  output all values for Nth variable*/

  select &vname.

    from sashelp.class

  ;

quit;

Super User
Super User
Posts: 7,046

Re: Need to find nth Variable from given dataset

Can you explain what you actually want to do?  Why would you be interested in the name of the Nth variable?  Many datasets are created without concern for variable (column) order.

You can get all of the variable names from dictionary view.

%let N=4;

proc sql noprint ;

  select name into :colname separated by ' '

  from dictionary.columns

  where libname='SASHELP'

     and memname='CLASS'

     and varnum = &n

;

quit;

%put Var &n is &colname ;

Respected Advisor
Posts: 3,156

Re: Need to find nth Variable from given dataset

For example, n=3:

%LET P=3;

proc sql;

  select name into :name FROM DICTIONARY.COLUMNS

  where libname='SASHELP' AND MEMNAME='CLASS' AND VARNUM=&P;QUIT;

  DATA WANT;

  set sashelp.class (keep=&name);

  run;

Haikuo

Super User
Posts: 11,343

Re: Need to find nth Variable from given dataset

And if you want the number of observations in a data set the variable NOBS in DICTIONARY.TABLES contains that information. If you want non-missing that's another kettle of fish.

Frequent Contributor
Posts: 88

Re: Need to find nth Variable from given dataset

Thanks  to a Tom and Hai.kuo..... and very Special thanks to Arthur.. you rock as always... This absolutely worked the way i want.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1720 views
  • 6 likes
  • 5 in conversation