Pass one variable values into an array

[hamza_saspg]

Hi,

I need to pass the elements/values of a character variable  into an array :

data have;
  input var $5. ;
datalines;
x1
x2
x3
.
.
.xn

run;

proc sql;

 select count(*) into: Nbrvars
  from dictionary.columns 
   where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;

  select var into:All_vars separated by " "
  from dictionary.columns 
   where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;
quit;

data want;
  set have;
  array test[&Nbrvars] &All_vars ;
 do i=1 to &Nbrvars. ;
   test[i]="test" /*or any other operation*/
 end;
run;

But the above approach do not give the expected result so I have all the elements of a character variable passed in an array then used to perform other operations.

Thanks in advanced for support.

[PaigeMiller]

Sounds like the XY problem to me. Please explain the big problem you are trying to solve. Please explain why you need these text strings in an array. What is wrong with leaving them in a long data set?

[hamza_saspg]

@PaigeMiller I have stored a variables name in one  character column contains  from another step and need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not, so I thought that arrays would be the best way to do

[ballardw]

It may help to describe what you will do with the array once you have one.

I would not expect that code to provide desired results. First the data step is sort of messed up as you have three blank values of Var instead of possibly the 2 expected (a simple  ; on a line by itself ends the datalines)

 where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;

For one thing UPCASE(MEMNAME) will never yield "have", it would be "HAVE".

Second Dictionary.columns does not have a variable named VAR. You can check on the content of a table with this code:

Proc sql;
  describe table dictionary.columns;
quit;

Which yields in the LOG:

create table DICTIONARY.COLUMNS
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name',
   type char(4) label='Column Type',
   length num label='Column Length',
   npos num label='Column Position',
   varnum num label='Column Number in Table',
   label char(256) label='Column Label',
   format char(49) label='Column Format',
   informat char(49) label='Column Informat',
   idxusage char(9) label='Column Index Type',
   sortedby num label='Order in Key Sequence',
   xtype char(12) label='Extended Type',
   notnull char(3) label='Not NULL?',
   precision num label='Precision',
   scale num label='Scale',
   transcode char(3) label='Transcoded?',
   diagnostic char(256) label='Diagnostic Message from File Open Attempt'
  );

Also the syntax would be : not missing(var) if you are looking for observations where the value of Var is not missing. The construct :  not missing  likely throws and error (did you you look at your log?).

So your SQL did not generate any values into the macro variables because the step stopped due to errors.

When I run a modified version of your data step with that SQL:

35   proc sql;
36
37    select count(*) into: Nbrvars
38     from dictionary.columns
39      where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;
                                                                            -------
                                                                            22
                                                                            76
ERROR 22-322: Syntax error, expecting one of the following: <, <=, <>, =, >, >=, ?, CONTAINS, EQ,
              EQT, GE, GET, GT, GTT, IS, LE, LET, LIKE, LT, LTT, NE, NET, ^=, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

40
41     select var into:All_vars separated by " "
42     from dictionary.columns
43      where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;
                                                                            -------
                                                                            22
                                                                            76
ERROR 22-322: Syntax error, expecting one of the following: <, <=, <>, =, >, >=, ?, CONTAINS, EQ,
              EQT, GE, GET, GT, GTT, IS, LE, LET, LIKE, LT, LTT, NE, NET, ^=, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

44   quit;

It would likely be much better to describe what you actually want to do. Placing all those values into an array is very likely not

[Tom]

No idea what you are trying to do, but if you want to fill and array with the values from a dataset variable then a simple DO loop should do.  You will need to know the number of observations in the dataset in advance to be able to size the array appropriately.

data have;
  input var $5. ;
datalines;
x1
x2
x3
xn
;


data _null_;
  call symputx('array_size',nobs);
  stop;
  set have nobs=nobs;
run;

data want;
  array test[&array_size] $5 _temporary_ ;
  if _n_=1 then do _n_=1 to &array_size while (not eof);
    set have(keep=var) end=eof;
    test[_n_]=var;
  end;
  ... rest of code that does something with the array...
run;

[ballardw]

Or if indeed only a single variable is of interest then perhaps proc transpose:

proc transpose data=work.have out=trans (drop=_name_)
   prefix =  var;
   var var;
run;

Which will do the element numbering for you for use with an array:

data want; 
    set trans;
    array v(*) var: ;
   /* what ever you are going to do with the array goes here*/
run;

[hamza_saspg]

@ballardw  my character column elements do not have an identic naming rule, so I do not have control on that elements and couldn't expect that content (to clarify more: my character column contains a variables names extracted from another step and need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not)

[ballardw]

---

@hamza_saspg wrote:

@ballardw  my character column elements do not have an identic naming rule, so I do not have control on that elements and couldn't expect that content (to clarify more: my character column contains a variables names extracted from another step and need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not)

---

---

@hamza_saspg wrote:

@ballardw  my character column elements do not have an identic naming rule, so I do not have control on that elements and couldn't expect that content (to clarify more: my character column contains a variables names extracted from another step and need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not)

---

I'm not sure what the "elements do not have an identic naming rule" has to do with anything.

If you are referring to the Proc transpose partial solution then the step is to place the VALUES of your current variable (what ever stuff may be in it) and place it on a single observation (a requirement for an array) and provide a variable name that is "nice" to work with an array.

And are you looking for "empty values of the VARIABLE" or "empty values of the extracted variable NAME"?

You say you have extracted names. If you get empty values for extracting a name then your process is seriously flawed.

Personally, given your link to that other discussion of PRX I think that you really need to start by describing what this whole process is supposed to do and what the actual expected output at the end is supposed to contain. You are talking about (poorly defined) bits of a "process" with providing a starting point or where it is supposed to go. It is very easy to start down a path that is extremely complicated by picking tools and trying to force the problem to fit the tool instead of describing the problem.

[hamza_saspg]

@Tom my character column contains a variables names extracted from another step and I need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not, so x1...xn is just an abbreviation of the variables names

[Tom]

---

@hamza_saspg wrote:

@Tom my character column contains a variables names extracted from another step and I need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not, so x1...xn is just an abbreviation of the variables names

---

So you do not want to retrieve DATA from the variable.  Instead you want to retrieve METADATA.

If the list is short enough (and it should be or you will have other troubles) then just put the list into a macro variable and then use the macro variable to define the variables that constitute the array.

proc sql noprint;
select nliteral(varname) into :varlist separated by ' '
  from my_varlis
;
quit;

data want;
  set have;
  array vars &varlist;
  .... rest of the data step that used the array named VARS ...
run;

[hamza_saspg]

@Tom Thanks for your proposition, but I think the logic of testing or creating an array from the metadata is not so efficient and maybe need some logic connected to the original data containing those variables and not just storing the variable names in one column.

This issue is in relationship with the following one Extract variable names using prx expression 

[Tom]

---

@hamza_saspg wrote:

@Tom Thanks for your proposition, but I think the logic of testing or creating an array from the metadata is not so efficient and maybe need some logic connected to the original data containing those variables and not just storing the variable names in one column.

This issue is in relationship with the following one Extract variable names using prx expression 

---

That is a different question.  Which variables to include in the list would be part of the step that made the VARLIST dataset.  Or at least the VARLIST dataset would have enough other metadata so that the step that extracts the list into a macro could use those other metadata values to decide which variables to select.

As to whether an ARRAY is useful I have no idea, but it is what you asked for.

Please explain the actual problem you are trying to solve.  That is the fastest way to a solution rather than getting bogged down in debugging a particular approach.  See https://xyproblem.info/

[PaigeMiller]

---

@hamza_saspg wrote:

@Tom my character column contains a variables names extracted from another step and I need to perform some conditional structure on those variables names stores in one column and test whether they are empty or not, so x1...xn is just an abbreviation of the variables names

---

Honestly, I think this is gibberish. and doesn't explain WHY you are doing this, even though three people have asked. Why do you need this?

[Reeza]

---

@hamza_saspg wrote:

Hi,

I need to pass the elements/values of a character variable  into an array :

data have;
  input var $5. ;
datalines;
x1
x2
x3
.
.
.xn

run;

proc sql;

 select count(*) into: Nbrvars
  from dictionary.columns 
   where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;

  select var into:All_vars separated by " "
  from dictionary.columns 
   where upcase(libname)="WORK" and upcase(memname)="have" and var not missing;
quit;

data want;
  set have;
  array test[&Nbrvars] &All_vars ;
 do i=1 to &Nbrvars. ;
   test[i]="test" /*or any other operation*/
 end;
run;

But the above approach do not give the expected result so I have all the elements of a character variable passed in an array then used to perform other operations.

Thanks in advanced for support.

---

That code wouldn't work - the SQL portion as 'var is not missing' refers to your have dataset which is not in your SQL query. 

If that have was your input, what do you expect as output? You say you need to "test whether they are empty or no"

Is that per variable or are you looking for rows that are fully empty. 

Instead of metadata I would recommend using the _character_ and _numeric_ automatic variables that automatically reference all numeric and character variables - arrays cannot be of mixed types so you'll need two arrays most likely. 

But if ultimately what you're after is the number of missing/empty values per column there are easier ways to do this than a data step. 

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-would-I-Count-Missing-Values-for-all-Columns...

And many other options. 

The best way to get an answer here is to show your input and the expected output based on that data, to avoid further back and forth due to the xy problem.

The XY problem is a communication problem encountered in help desk, technical support, software engineering, or customer service situations where the question is about an end user's attempted solution (X) rather than the root problem itself (Y or Why?).^[1]

The XY problem obscures the real issues and may even introduce secondary problems that lead to miscommunication, resource mismanagement, and sub-par solutions. The solution for the support personnel is to ask probing questions as to why the information is needed in order to identify the root problem Y and redirect the end user away from an unproductive path of inquiry.^[2][3]