Non-Indexed Arrays and Do Loops

[belboy]

Hi,

I'm working with a dataset that contains 389 variables and 19 obs. 

Each variable has the value "Y" or "N". 

I want to replace the value "Y" with the actual variable name. 

Here is my code:

data Doappname;
set work.dna;

drop 'User Name'n 'Date last on'n 'Change Days'n
'Expiration Date'n Allow Master User'n
F1 F2 F3 F4 F5 F6
F7 F8 F9 F10;

array test {*}_CHARACTER_ ;
do i= 1 to dim(test);
if test{i} eq "Y" then do;

test{i} = vname(test{i});
output;
output;
end;
end;
run;

SAS log:

NOTE: There were 19 observations read from the data set WORK.DNA.
NOTE: The data set WORK.DOAPPNAME has 50 observations and 389 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

Nothing changes except for an increase in observations and an added "i" column showing iterations.  Every variable still has the value "Y" instead of a column name.

I simply just want to replace all instances of "Y" with the actual variable name.  Any help is appreciated.

[RW9]

Its the positioning of the output statement.  Basically when you output, you write a record out to the file, and then start a new one, hence why you get more observations as one is written out each time a Y is found.  What you want is:

data doappname;
  set work.dna;
  array test {*} _character_ ;
  do i=1 to dim(test);
    test{i}=ifc(strip(test{i})="Y",vname(test{i}),test{i});
  end;
run;

Do note however that 389 variables, and only 19 obs really doesn't sound like good data modelling.

[belboy]

Thanks, but unfortunately that still did not work.  My values remained "Y".  

[RW9]

Post some test data in the form of a datastep, not much point in me trying to guess.

[ballardw]

You are also likely to encounter problems with the lengths of your variables. If you current values are "Y" or (guessing) "N" then it is likely that the existing length of the variable is not long enough to hold the actual variable name.

See this example:

data example;
   LongishVariable = 'Y';
run;

data want;
   set example;
   array test _character_;
   do i=1 to dim(test);
      if test[i]='Y' then test[i]=vname(test[i]);
   end;
   drop i;
run;

Note that in the want set the value of the variable is now L instead of Y as the applied as much of the name that variable of length 1 would hold. The correction to allow that would be to have LENGTH statement prior to the set statement for the variables involved so that the length would hold the name:

data want;
   length LongishVariable $ 15;
   set example;
   array test _character_;
   do i=1 to dim(test);
      if test[i]='Y' then test[i]=vname(test[i]);
   end;
   drop i;
run;

Also note that there really isn't a reason to have OUTPUT statement as it is implied at the end of the data step.

[Tom]

You will probably have trouble storing the variable name into a variable that is designed to hold only 'Y' or 'N'.  Those variables are probably only one byte long so they could only store the first letter of the name.

Do you know the names of your character variables?

What is with the DROP statement?  Are those the character variables where you don't want to make this change?

Here is a program assuming you have a list of names in a macro variable. Notice that the ARRAY statement is before the SET so that the length of the variables can be defined long enough to hold a variable name.

data want ;
   array _fix $32 &varlist ;
   set have ;
   do i=1 to dim(_fix);
     if upcase(_fix(i))='Y' then _fix(i)=vname(_fix(i));
   end;
run;

There are many ways to use code to generate the macro variable with the list of names.  Here is one using PROC TRANSPOSE to get the list of names of the character variables and PROC SQL to make the macro variable.

proc transpose data=have (obs=0) out=names ;
  var _character_;
run;
proc sql noprint;
  select nliteral(_name_) 
    into :varlist separated by ' '
  from names
  ;
quit;

[belboy]

here is a sample of dataset

AcID, Menu 'Menu-File'n 'Menu-File-Open'n

UGTER Y Y Y Y Y
UeABC1 N N N N Y
UAeCB269 N N N N Y
UAwCG2 N N N N Y

I just want the "Y" values to say Menu, 'Menu-File'n, 'Menu-File-Open'n, DNA, DNA2 - instead of "Y".  

@Tom correct -  I used the drop statement to prevent inclusion of those variables.

[ballardw]

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

---

@belboy wrote:

here is a sample of dataset

AcID, Menu 'Menu-File'n 'Menu-File-Open'n

UGTER Y Y Y Y Y
UeABC1 N N N N Y
UAeCB269 N N N N Y
UAwCG2 N N N N Y

I just want the "Y" values to say Menu, 'Menu-File'n, 'Menu-File-Open'n, DNA, DNA2 - instead of "Y".   

---

That list does NOT give us information about your existing data set such as lengths of variables which the macro would show us.

Another option would be to create FORMATS for the variables of interest such as

proc format lib=work;
value $Menu_file
'Y'= "'Menu-File'n";
run;

and then use the format when you want to see that text:

format 'Menu-File'n menu_file. ;

Note that you can't use the name literal for the Value statement to name the format.

though I have to say I really don't understand wanting to see 'Menu-File'n as a value, 'Menu-File' would make more sense to me.

[Tom · Accepted solution]

So this is how you can share your data with someone on the forum. Post it in the form of code they can run.

options validvarname=any ;
data have ;
  input AcID :$10. ( Menu 'Menu-File'n 'Menu-File-Open'n) (:$1.);
cards;
UGTER Y Y Y Y Y
UeABC1 N N N N Y
UAeCB269 N N N N Y
UAwCG2 N N N N Y
;

To transform that dataset in the way you want you will need to make the variables longer.  Which means you need to set the length BEFORE you reference the dataset.  Note that I set the length longer than $32 since it needs room for the quotes and the N suffix to represent the name in name literal form.  It also needs room in case there are any quotes in the name.

data want ;
  array _fix $40 Menu 'Menu-File'n 'Menu-File-Open'n ;
  set have ;
  do i=1 to dim(_fix);
    if _fix(i)='Y' then _fix(i)=nliteral(vname(_fix(i)));
  end;
run;

Here is the result for your file.

Obs    Menu    Menu-File       Menu-File-Open       AcID        i

 1     Menu    "Menu-File"N    "Menu-File-Open"N    UGTER       4
 2     N       N               N                    UeABC1      4
 3     N       N               N                    UAeCB269    4
 4     N       N               N                    UAwCG2      4

You should probably DROP the loop counter variable I.

You could also just transpose the data first. Then manipulate the value and transpose it back.

proc transpose data=have out=middle ;
  by Acid notsorted ;
  var Menu 'Menu-File'n 'Menu-File-Open'n ;
run;

data middle ;
  length col1 $40 ;
  set middle ;
  if col1='Y' then col1=nliteral(_name_) ;
run;

proc transpose data=middle out=want (drop=_name_) ;
  by acid notsorted ;
  id _name_ ;
  var col1 ;
run;

[belboy]

Thanks @Tom!

Also thanks @RW9 for your input as well 🙂