SAS. how to define an array using a list of variables stored in a macro variable

Question

I have a list of 15 similar variables that I want to loop through syntax to recode as null. I will be adding more variables to this list so that's why I went with a macro. I created a macro variable that stores a list of variables and another macro variable that stores the count of variables. I am having trouble defining the array. I got the code below to work, but it only goes through varlist1, and not the other 14 variables (varlist2-varlist15)... which is what I told it to do but I can't figure out how to expand it to the full list of variables without breaking the code. I searched through the forums and SAS articles but couldn't find an answer. I'm fairly new to arrays so I'm sure it's something simple I don't understand. appreciate any help and let me know if I can post better next time. first one. :)

this is my list in &varlist.

(
NEW_CREWSTATE1A 
NEW_EquFailText 
NEW_ExpMedNotList 
NEW_NARRATIVEDISTRESS 
NEW_NarrativeRecovery 
NEW_NarrativeSearch 
NEW_OthEquText 
NEW_PersReqMedTxt 
NEW_ProbEnct 
NEW_Recommend 
NEW_RescueSwimProbText 
NEW_RescuerProb 
NEW_SRUConfigTxt 
NEW_equimalissue 
new_MedDiffText 
)

code:

proc sql noprint;
select count(name)  into :numVar
from sashelp.vcolumn
where upcase(LIBname)="STAGING" and UPCASE(memname)="RESC_NARRATIVEMERGE" and UPCASE(name) like 'NEW_%';
quit;
%put &numVar;

proc sql noprint;
select distinct(name)  into :varlist1- 
from sashelp.vcolumn
where upcase(LIBname)="STAGING" and UPCASE(memname)="RESC_NARRATIVEMERGE" and UPCASE(name) like 'NEW_%';
quit;


data staging.RESC_NARRATIVEMERGE2;
set  staging.RESC_NARRATIVEMERGE;
    array narrative_array {*} &varlist1. ;
    do i=1 to dim(narrative_array);

    
    
    if strip(narrative_array{i})='N/A' then narrative_array{i}='';
    if strip(narrative_array{i})='N/A.' then narrative_array{i}='';
    if strip(narrative_array{i})='NA' then narrative_array{i}='';
    if strip(narrative_array{i})='NONE' then narrative_array{i}='';
    if strip(narrative_array{i})='NONE NOTED' then narrative_array{i}='';
    if strip(narrative_array{i})='NONE EXPERIENCED' then narrative_array{i}='';
    if strip(narrative_array{i})='NONE TO REPORT' then narrative_array{i}='';
    if strip(narrative_array{i})='NONE.' then narrative_array{i}='';
    if strip(narrative_array{i})='NOT APPLICABLE' then narrative_array{i}='';
    

    end;
run;

Do you expect to ever have more than 2000 variables in the array? If not then just the list into ONE macro variable. — Tom
– Tom, Commented Oct 6, 2022 at 3:09

Tom · Accepted Answer · 2022-10-06 03:19:32Z

3

The only thing you need to define an array is the actual list of variables names.

array narrative_array Avar Anothervar Someothervar ;

So just put the list of variable names into ONE macro variable.

proc sql noprint;
select nliteral(name) 
  into :varlist separated by ' '
from dictionary.columns
where libname="STAGING"
  and memname="RESC_NARRATIVEMERGE"
  and UPCASE(name) like 'NEW^_%' escape '^'
;
quit;

Note that there is no need to count them, but if you want the count then SQL will have already stored the count into the macro variable SQLOBS. Perhaps you can use the count to decide whether or not you need to define the array at all.

data staging.RESC_NARRATIVEMERGE2;
  set staging.RESC_NARRATIVEMERGE;
%if &sqlobs %then %do;
  array narrative_array &varlist ;
  do index=1 to dim(narrative_array);
    if left(compbl(narrative_array[index])) in
       ('N/A','N/A.','NA','NONE','NONE NOTED','NONE EXPERIENCED'
       ,'NONE TO REPORT','NONE.','NOT APPLICABLE')
      then narrative_array[index]=' ';
  end;
  drop index ;
%end;
run;

answered Oct 6, 2022 at 3:19

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Epiwatson7 Over a year ago

Thank you Tom. The code runs now; I'm not getting any errors, but it isn't changing the values to blank. Do you have any ideas what's going wrong?

Tom Over a year ago

Check the values of the variables. Check for "hidden characters", like tab, linefeed , non-breaking spaces, etc.

Richard · Accepted Answer · 2022-10-06 12:23:44Z

Look at your variable name selection criteria

where upcase(LIBname)="STAGING" 
  and UPCASE(memname)="RESC_NARRATIVEMERGE"
  and UPCASE(name) like 'NEW_%'
;

You are looking for variable names that start with NEW_. The DATA Step has a naming list syntax that selects variables based on prefix (<prefix>:), and that list can be used to specify the members of an array.

If there are no variables that match the specified list the array will have zero elements and the log will show the message "WARNING: Defining an array with zero elements." A loop can be coded simply as 1 to DIM(<array-name>) and no iterations will occur because the DIM() result is 0

data ...
  set STAGING.RESC_NARRATIVEMERGE;
  array NEW NEW_:;

As shown by @Tom your wallpaper of tests to transform non-values to blanks can be changed to a IN list for better performance and human clarity.

Collectives™ on Stack Overflow

SAS. how to define an array using a list of variables stored in a macro variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related