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

Reply
Occasional Contributor
Posts: 7

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

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

Attachment
Regular Contributor
Posts: 151

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

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.

Occasional Contributor
Posts: 15

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

[ Edited ]

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

 

???????

Esteemed Advisor
Posts: 7,300

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

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

 

Occasional Contributor
Posts: 15

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

[ Edited ]

thank you, I just tried to avoid those workarounds.

 

Super User
Super User
Posts: 6,383

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

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.

 

Occasional Contributor
Posts: 15

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

thank you. Now it's clear. I searched for "SAS variables Wildcard" and in none of the results is a hint like this
Ask a Question
Discussion stats
  • 6 replies
  • 357 views
  • 0 likes
  • 5 in conversation