BookmarkSubscribeRSS Feed
Niala
Calcite | Level 5

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

6 REPLIES 6
Keith
Obsidian | Level 7

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.

defaz
Fluorite | Level 6

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

 

???????

art297
Opal | Level 21

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

 

defaz
Fluorite | Level 6

thank you, I just tried to avoid those workarounds.

 

Tom
Super User Tom
Super User

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.

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2709 views
  • 1 like
  • 5 in conversation