topic Need to find nth Variable from given dataset in ODS and Base Reporting

Need to find nth Variable from given dataset

Tushar — Mon, 29 Oct 2012 14:53:30 GMT

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.

Re: Need to find nth Variable from given dataset

art297 — Mon, 29 Oct 2012 15:27:33 GMT

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;

Re: Need to find nth Variable from given dataset

Tom — Mon, 29 Oct 2012 15:27:53 GMT

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 ;

Re: Need to find nth Variable from given dataset

Haikuo — Mon, 29 Oct 2012 15:28:00 GMT

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

Re: Need to find nth Variable from given dataset

ballardw — Mon, 29 Oct 2012 15:48:55 GMT

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.

Re: Need to find nth Variable from given dataset

Tushar — Mon, 29 Oct 2012 19:48:43 GMT

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