topic Re: How to reference a variable if I only know the position in the PDV? in SAS Programming

How to reference a variable if I only know the position in the PDV?

Patrick — Sun, 13 Nov 2022 06:24:23 GMT

Hi Experts,
Is there any syntax that allows me to address a variable in a data step if the only thing I know is that it's the first variable in the PDV?

What I'm dealing with are Excel sources for which I'd like to implement code as robust as possible.

I'd like code that can deal with:

no column header defined (=cell A1 is empty) -> character variable with name A
column header defined -> variable has name of column header
all cells empty -> character variable
cells with only digits -> numerical variable

What I'm currently doing and what will work for my data is:

libname src xlsx "<path>/<name>.xlsx";
data want;
  set src.sheet;
  if substr(cats(of _all_),1,1)='#' then delete;
run;

What I'd like to do is something like below (which of course doesn't work if there are numerical and character variables in the pdv).

libname src xlsx "<path>/<name>.xlsx";
data want;
  set src.sheet;
  array allvars {*} _all_;
  if cats(allvars[1])='#' then delete;
run;

I'm not after alternative code like querying the dictionary tables etc. but really curious if there is another way to reference a variable without having to know the name in advance other than using an array.

Re: How to reference a variable if I only know the position in the PDV?

Kurt_Bremser — Sun, 13 Nov 2022 08:11:32 GMT

You can get the name from DICTIONARY.COLUMNS, or use the OPEN and VARNAME functions in a data step.

Re: How to reference a variable if I only know the position in the PDV?

Ksharp — Sun, 13 Nov 2022 09:49:19 GMT

Kurt has already given you the solution.

Also could try CALL VNEXT() if you want the variable name in PDV !

data _null_;
 set sashelp.class;
length vname $ 40;
do i=1 by 1 until(missing(vname));
 call vnext(vname);
 put i= vname=;
end;

stop;
run;

i=1 vname=Name
i=2 vname=Sex
i=3 vname=Age
i=4 vname=Height
i=5 vname=Weight
i=6 vname=vname
i=7 vname=i
i=8 vname=_ERROR_
i=9 vname=_N_
i=10 vname=

Re: How to reference a variable if I only know the position in the PDV?

Patrick — Sun, 13 Nov 2022 10:03:47 GMT

Thank you for your time @Ksharp and @Kurt_Bremser looking into my question and providing an answer.

I know that I could query the dictionary tables but as stated in my question "I'm not after alternative code like querying the dictionary tables..."

I was wondering if there is any data step syntax I don't know of that lets me reference a variable by position in the PDV. I don't believe this exists other than via the varname() function and DDV but then there are still many things I don't know.

Re: How to reference a variable if I only know the position in the PDV?

Ksharp — Tue, 15 Nov 2022 11:44:08 GMT

/*
Hi Patrick, How about this one ?
*/
%let position=1;

data have;
 set sashelp.class;

if _n_=1 then do;
length vname $ 40;
retain vname;
do i=1 by 1 until(missing(vname));
  call vnext(vname);
  if i=&position then leave;
end;
end;

if strip(vvaluex(vname))=:'A' then flag='Y';
run;

Re: How to reference a variable if I only know the position in the PDV?

Patrick — Tue, 15 Nov 2022 20:07:33 GMT

@Ksharp Yes, I like that. It's dynamic but still simple and the source variable can be either character or numeric with the code not even writing some type conversion note.

I believe you need a small change to your proposed code to ensure that call vnext() gets always executed until the condition is met (like for &position = 3).

%let position=1;

data have;
  set sashelp.class;

  if _n_=1 then
    do;
      length vname $ 32;
      retain vname;
      do i=1 by 1 until(missing(vname));
        call vnext(vname);
        if i=&position then leave;
      end;
    end;

  if vvaluex(vname)=:'A' then flag='Y';
run;

Re: How to reference a variable if I only know the position in the PDV?

Ksharp — Tue, 15 Nov 2022 11:45:48 GMT

Opps. Patrick, I forgot to test other 'position'. As your wish I have changed my code as yours.