BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tushar
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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 ;

Haikuo
Onyx | Level 15

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

ballardw
Super User

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.

Tushar
Obsidian | Level 7

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

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
  • 4173 views
  • 6 likes
  • 5 in conversation