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
--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.
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;
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;
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 ;
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
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.
Thanks to a Tom and Hai.kuo..... and very Special thanks to Arthur.. you rock as always... This absolutely worked the way i want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.