## Acces data in table by row and col number

Frequent Contributor
Posts: 134

# Acces data in table by row and col number

Hello,

knowing i=3 and j=Gender,

I would like to find the value in a SAS table "WORK.Mytable" located in row 3 and column "Gender"

In a matricial language I would simply say "MyTable[i,j]"

How would you do this in SAS ? (without SAS/IML)l

Super User
Posts: 10,787

## Re: Acces data in table by row and col number

```%let i=3;
%let j=name;

proc sql noprint;
select &j into : v1- : v&i from sashelp.class ;
quit;

%put &&v&i;
```

Ksharp

Frequent Contributor
Posts: 134

## Re: Acces data in table by row and col number

Is this the fastest way ?

This is working, but not efficient for iterations.

My algorithmic complexity is O(n²) with n going up to 300k.

(Just crashed sasbi server with n=50k)

%macro zut();

%let nobs=5;

%do i=1 %TO &nobs;

%do j=%eval(&i+1) %TO &nobs;

proc sql noprint;

select Gender into : m1- : m&i from &WorkingTable ;

quit;

/*%put &&m&i;*/

proc sql noprint;

select Gender into : n1- : n&j from &WorkingTable ;

quit;

/*%put &&n&j;*/

%if &&m&i = &&n&j %then %do;

%put DoingSomething;

%end;

%end;

%end;

%mend zut;

%zut()

Occasional Contributor
Posts: 13

## Re: Acces data in table by row and col number

Hi

My two cents:

macro approach:

%macro m(ds=, i=, j=);
%local dsid rc varnum func;
%let dsid = %sysfunc(open(&ds));
%let varnum = %sysfunc(varnum(&dsid, &j));
%let rc = %sysfunc(fetchobs(&dsid, &i));
/*%if %sysfunc(vartype(&dsid, &varnum)) eq N
%then %let func = getvarn;
%else %let func = getvarc;
*//*This is refactored*/
%let func = getvar%sysfunc(vartype(&dsid, &varnum));

%let value = %sysfunc(&func(&dsid, &varnum));
%let rc = %sysfunc(close(&dsid));
%put &value;

%mend;
%m(ds=sashelp.prdsal2, i=3, j=actual);

Data step approach:

%let ds = sashelp.prdsal2;

%let i = 3;

%let j = actual;

data _null_;

dsid = open("&ds");

varnum = varnum(dsid, "&j");

rc = fetchobs(dsid, &i);

if vartype(dsid, varnum) eq "N" then valuen = getvarn(dsid, varnum);

else valuec = getvarc(dsid, varnum);

rc = close(dsid);

put valuen= valuec=;

run;

but this is shorter I think:

%let i=3;
%let j=name;

proc sql noprint;
select &j into : v from sashelp.class(firstobs=&i obs=&i);
quit;

%put &v;

Message was edited: added another approach

Super User
Posts: 10,787

## Re: Acces data in table by row and col number

You are right.

If you want faster, try this directly accessing.

```%let i=3;
%let j=name;

data _null_;
n=&i ;
set sashelp.class point=n;
call symputx('val',  &j  );
stop;
run;
%put &val    ;

```

Ksharp

Occasional Contributor
Posts: 13

## Re: Acces data in table by row and col number

%let i=3;

%let j=name;

data _null_;

n=&i ;

set sashelp.class point=n;

call symputx('val',  &j  );

/*bug fix*/

stop;

/*end bug fix*/

run;

%put &val    ;

Super User
Posts: 10,787

## Re: Acces data in table by row and col number

Thanks .TD .

I have realized this bug. You posted it so quickly before I type my code again.

Frequent Contributor
Posts: 134

## Re: Acces data in table by row and col number

Should this be much faster ?

I don't see a difference.

Maybe you have a better solution for going twice through every line of a table ?

Super User
Posts: 23,776

## Re: Acces data in table by row and col number

What are you trying to do in the end?

it's easy enough to go through a line twice in SAS data step.

Super User
Posts: 10,787

## Re: Acces data in table by row and col number

Yes. I used Random accessing method. not like common method (sequential access method).

It would be faster.

New Contributor
Posts: 3

## Re: Acces data in table by row and col number

Dear,

When you say access to table at row and column number, it is not possible because SAS does not assure the order of the colum. You can have a look with the definition of the table inside the view sashelp.vcolumns.

SO the response of D is well.

But a other way could be, using the name ( and not the number of column) ;

data _null_; set WORK.Mytab;

if _n_ eq &i then do;

call symput ('mywishedvalue',&mycolumn);

end;

run;

it depend of the datset size.

Super Contributor
Posts: 1,636

## Re: Acces data in table by row and col number

%let i=3;

%let j=name;

data _null_;

set sashelp.class(firstobs=&i obs=&i);

call symputx('ll',  &j  );

run;

%put &ll;

Discussion stats
• 11 replies
• 480 views
• 6 likes
• 6 in conversation