05-30-2014 11:15 AM
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
05-30-2014 11:48 AM
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.
03-06-2017 03:14 AM - edited 03-06-2017 03:17 AM
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;
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
03-06-2017 09:11 AM
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; email@example.com firstname.lastname@example.org 123 First St NYC email@example.com firstname.lastname@example.org 1563 2nd St Chicago email@example.com firstname.lastname@example.org 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
03-06-2017 09:18 AM
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.