BookmarkSubscribeRSS Feed
mathias
Quartz | Level 8

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

11 REPLIES 11
Ksharp
Super User

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
Quartz | Level 8

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
Calcite | Level 5 TD
Calcite | Level 5

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

Ksharp
Super User

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
Calcite | Level 5 TD
Calcite | Level 5

%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
Super User

Thanks .TD .

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

mathias
Quartz | Level 8

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
Super User

What are you trying to do in the end?

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

Ksharp
Super User

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

It would be faster.

FabienLACREUSE
Fluorite | Level 6

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
Lapis Lazuli | Level 10

%let i=3;

%let j=name;

data _null_;

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

call symputx('ll',  &j  );

run;

%put ≪

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
  • 11 replies
  • 1578 views
  • 6 likes
  • 6 in conversation