topic Re: Acces data in table by row and col number in SAS Programming

Acces data in table by row and col number

mathias — Thu, 10 May 2012 08:16:23 GMT

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

Re: Acces data in table by row and col number

Ksharp — Thu, 10 May 2012 09:48:06 GMT

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

Re: Acces data in table by row and col number

mathias — Thu, 10 May 2012 10:30:44 GMT

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;

%mend zut;

%zut()

Re: Acces data in table by row and col number

TD — Thu, 10 May 2012 11:02:07 GMT

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

Re: Acces data in table by row and col number

Ksharp — Thu, 10 May 2012 11:30:48 GMT

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

Re: Acces data in table by row and col number

TD — Thu, 10 May 2012 11:41:22 GMT

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

Re: Acces data in table by row and col number

Ksharp — Thu, 10 May 2012 12:43:49 GMT

Thanks .TD .

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

Re: Acces data in table by row and col number

mathias — Thu, 10 May 2012 13:20:27 GMT

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 ?

Re: Acces data in table by row and col number

FabienLACREUSE — Thu, 10 May 2012 13:33:52 GMT

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.

Re: Acces data in table by row and col number

Reeza — Thu, 10 May 2012 14:19:17 GMT

What are you trying to do in the end?

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

Re: Acces data in table by row and col number

Ksharp — Thu, 10 May 2012 14:46:39 GMT

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

It would be faster.

Re: Acces data in table by row and col number

Linlin — Thu, 10 May 2012 15:12:19 GMT

%let i=3;

%let j=name;

data _null_;

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

call symputx('ll', &j );

run;

%put &ll;