Acces data in table by row and col number

[mathias]

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

[Ksharp]

How about:

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


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

%put &&v&i;

Ksharp

[mathias]

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()

[TD]

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;

KSharp: your code is short

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

[Ksharp]

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

消息编辑者为：xia keshan

消息编辑者为：xia keshan

[TD]

%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    ;

[Ksharp]

Thanks .TD .

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

[mathias]

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 ?

[Reeza]

What are you trying to do in the end?

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

[Ksharp]

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

It would be faster.

[FabienLACREUSE]

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.

[Linlin]

%let i=3;

%let j=name;

data _null_;

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

call symputx('ll',  &j  );

run;

%put ≪