topic Re: Loop over variables with name ending character. in SAS Programming

Loop over variables with name ending character.

Scottmeup — Sun, 27 May 2018 02:23:52 GMT

Hi, I'm trying to work out if it's possible to use arrays and loops to parse over or reference variables that don't end in a number?

My variable names would look something like

v1a v1b v1c x1 v2a v2b v2c x2 v3a v3b v3c x3

I'm interested in parsing over those variables starting with v with the use of arrays and loops but i'm having a bit of difficulty. This is completely incorrect as far as Sas is concerned. I can't even reference v[i]b.

array v[3] = v1a--v1c;
do i = 1 to 3;
    if v[i]b=1 or v[i]c=1 then output_variable=1;

end;

The other thought that came to me was to put the entire set of variables in an array, iterate over them and use vname() but I can't seem to get the proper understanding for how to make that work either.

Should I suck it up and manually code the variable names, or is it possible to parse over variables with this naming structure using arrays?

I'd really like to tackle this as dynamically as possible.

Re: Loop over variables with name ending character.

PeterClemmensen — Sun, 27 May 2018 06:08:24 GMT

I'm not sure what you want to do with the variables, but you can get a list of variables not ending with a number from a data set like this

data MyData;
input Var OtherVar Var1 Var2;
datalines;
1 2 3 4
run;

proc sql noprint;
select name into :vars separated by " " from dictionary.columns 
where memtype="DATA" and memname="MYDATA" and anydigit(strip(reverse(name))) ne 1;
quit;

%put &vars.;

You can then use &vars. to put the relevant variables in an array and loop over it like this

data testing(drop=i);
   set MyData;
   array SomeArray {*} &vars.;
   do i=1 to dim(SomeArray);
      SomeArray[i]=100;
   end;
run;

Re: Loop over variables with name ending character.

Quentin — Sun, 27 May 2018 12:42:23 GMT

Can you edit your question to show a few records of example data you HAVE, and also show the data you WANT?

It's possible you could do what you want with a two-dimensional array. Often if I am tempted to use a two-dimensional array, I take that as a sign that my data are poorly structured. And I end up transposing the data into a format that will make it easier to work with.

That said, a two-dimensional array approach might look something like:

data have ;
  input id v1a v1b v1c x1 v2a v2b v2c x2 v3a v3b v3c x3 ;
  cards ;
1  1  2  3  4  5  6  7  8  9  10  11  12
2 10 20 30 40 50 60 70 80 90 100 110 120
3  0  1  0  0  0  0  0  0  0   0   0   0
4  0  0  0  0  0  0  1  0  0   0   0   0
;

data want ;
  set have ;

  array v{3,3} v: ; *a little risky, assumes v* variables are in right order ; 

  do i=1 to dim1(v) ;

    *debugging, just to show how two-dimensional array works ;
    do j=1 to dim2(v) ;
      put id= v{i,j}= ;
    end ; 

    *v{i,2} will be v1b when i=1, v2b when i=2 etc ;
    if v{i,2}=1 or v{i,3}=1 then output_variable=1 ;
  end ;

run ;

Re: Loop over variables with name ending character.

art297 — Sun, 27 May 2018 13:38:12 GMT

Here is one way:

data have ;
  input id v1a v1b v1c x1 v2a v2b v2c x2 v3a v3b v3c x3 ;
  cards ;
1  1  2  3  4  5  6  7  8  9  10  11  12
2 0 1 1 40 0 1 0 80 1 1 1 120
3  0  1  0  0  0  0  0  0  0   0   0   0
4  0  0  0  0  0  0  1  0  0   0   0   0
;

data want ;
  set have ;

  array a_v{*} v1a--x3 ;
  array output_variable(3);
  
  do i=1 to 3;
    output_variable(i)=0;
  end;
  
  do i=1 to dim(a_v) ;
    if anydigit(strip(reverse(vname(a_v(i))))) ne 1 then do;
      if a_v(i) eq 1 then output_variable(input(compress(vname(a_v(i)),,'kd'),8.))=1;
    end;
  end;
run ;

Art, CEO, AnalystFinder.com

Re: Loop over variables with name ending character.

mkeintz — Sun, 27 May 2018 22:16:45 GMT

While I would use @PeterClemmensen's solution, leveraging the information in the sql-only dictionary table, it might be informative to consider a preliminary data step to do the same. Use that data step to write an array declaration to a temporary file, which is subsequently %INCLUDEd in your final data step:

filename t temp;
data _null_;
  set have (obs=1);
  array candidates{*} v1a--x3;
  file t;

  put 'array myvars {*} ';
  do i=1 to dim(candidates);
    _name=vname(candidates{i});
    if not(anydigit(char(_name,length(_name)))) then put +1 _name @;
  end;
  put  ';';
run;


options source2;
data want;
  set have;
  %include tmp;

  do i=1 to dim(myvars);
    ** do your array element processing here **;
  end;
  
run;

The "options source2" tells SAS to print to the log all sas statements in the %included file.

Re: Loop over variables with name ending character.

s_lassen — Mon, 28 May 2018 08:29:55 GMT

One possibility is to use a two-dimensional array, and use a macro to generate the variable names in the correct order, e.g.:

%macro array_names(prefix,suffix1,suffix2);

%local i j;

%do i=1 %to %sysfunc(countw(&suffix1));

%do j=1 %to %sysfunc(countw(&suffix2)); &prefix.%scan(&suffix1,&i)%scan(&suffix2,&j)%end;

%end;

%mend;

data _null_;

retain v1a 1 v1b 2 v1c 3 x1 99 v2a 4 v2b 5 v2c 6 x2 999 v3a 7 v3b 8 v3c 9 x3 0;

array v(3,3) %array_names(v,1 2 3,a b c);

do i=1 to 3;

do j=1 to 3;

put v(i,j);

end;

run;

I suggest using a macro because that is a good way of making sure you get the right variable names in the right order. So, instead of v[1]b you use v[1,2] etc.