DATA Step, Macro, Functions and more

Acces data in table by row and col number

Reply
Frequent Contributor
Posts: 127

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,041

Re: Acces data in table by row and col number

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

Frequent Contributor
Posts: 127

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

KSharp: your code is short Smiley Wink

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,041

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

消息编辑者为:xia keshan

消息编辑者为:xia keshan

Occasional Contributor TD
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,041

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: 127

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: 19,855

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,041

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 ≪

Ask a Question
Discussion stats
  • 11 replies
  • 449 views
  • 6 likes
  • 6 in conversation