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
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.
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
???????
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
thank you, I just tried to avoid those workarounds.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.