topic Re: Using column wildcard (:) with retain statement in a datastep in SAS Programming

Using column wildcard (:) with retain statement in a datastep

Niala — Fri, 30 May 2014 15:15:50 GMT

Hi,

I have a datastep where I need to use a retain statement to reorder variables.

When using the column wildcard, it will reorder my columns in a different way.

The problem is variables wil vary from one week to another. I might end up with 12 account numbers one week and only 3 the following one.

How can I make that work?

Please see attached

Re: Using column wildcard (:) with retain statement in a datastep

Keith — Fri, 30 May 2014 15:48:03 GMT

You need to put the RETAIN statement before the SET statement. The order of variables cannot be changed once the SET statement has been read, in your example even the expected code is wrong.

Re: Using column wildcard (:) with retain statement in a datastep

defaz — Mon, 06 Mar 2017 08:17:49 GMT

Anyway: retain with wildcard ( colon ) does not work....:

data _null_;
file _webout;
  length _out $2000.;
  retain PE12: PE3: IE12: IE3: Z1 0;
  set progdata2 end=eof;
  if _N_ eq 1 then do;
......
run;

gives an EMPTY result set, but

data _null_;
file _webout;
  length _out $2000.;
  retain PE12_1-PE12_99 PE3: IE12: IE3: Z1 0;
  set progdata2 end=eof;
  if _N_ eq 1 then do;
.....
run;

gives correct data (as expected for variables PE12_1.....PE12_12)

Rest of code and data unchanged

???????

Re: Using column wildcard (:) with retain statement in a datastep

art297 — Mon, 06 Mar 2017 14:11:14 GMT

Use PROC SQL to write out your retain statement for you. e.g.:

data ds01;
  input acct1 $6. acct2 $6. custno $4. email1 $16. email2 $16. address  $16. city $16.;
  datalines;
1234561405680001a@a.com         b@a.com         123 First St    NYC             
8518596165610002n@b.com         r@b.com         1563 2nd St     Chicago         
6641691008540003m@c.com         g@c.com         1986 3rd st     San Francisco   
;
run;

proc sql noprint;
  select name into :retains separated by ' '
    from dictionary.columns
      where libname=upcase('work') and
            memname=upcase('ds01') and
            (name like 'email%' or name like 'acct%')
        order by name
  ;
quit;

data ds02_expected;
  retain custno address city &retains.;
  set ds01;
run;

Art, CEO, AnalystFinder.com

Re: Using column wildcard (:) with retain statement in a datastep

Tom — Mon, 06 Mar 2017 14:18:25 GMT

Here is the reason why you cannot use : wildcard in a retain statement intended to re-order variables.

The : wildcard only works with variables AFTER they are already defined.

Using RETAIN to reorder variables requires that the RETAIN statement appear BEFORE the variables are defined.

Re: Using column wildcard (:) with retain statement in a datastep

defaz — Tue, 07 Mar 2017 05:13:01 GMT

thank you, I just tried to avoid those workarounds.

Re: Using column wildcard (:) with retain statement in a datastep

defaz — Tue, 07 Mar 2017 05:22:25 GMT

thank you. Now it's clear. I searched for "SAS variables Wildcard" and in none of the results is a hint like this