Reorder variables with retain, some problem using prefix

[Onizuka]

Hello,

I want to reorder lot of variables but I can't reorder them using the ' : '

Like :

data have ;
format a_1 a_2 a_3 b_1 b_2 d c $2. ;
input a_1 a_2 a_3 b_1 b_2 d c ;
cards;
a1 a2 a3 b1 b2 d c
a1 a2 a3 b1 b2 d c
;

Data want ;
retain d b: a: ;
set have ;
run ;

Is there a "trick" to do this on a retain without using a1-a3 b1-b2 ? Because on my real datas, there is no 1,2,3,4, ....

Thanks,

Mateo

[PeterClemmensen]

The reason why this doesn't work is that when the data step hits the Retain Statement during compilation, it does not yet know what variables to look for with the colon operator because it has not yet reached the Set Statement. 

I think you have to read the relevant variable names through metadata to achieve this. However, it may be more convenient just typing out the variable names. So if you are still interested, I will provide the code.

[Onizuka]

Oh yes... true... Thank you @PeterClemmensen .. !

I have a lot of variables, so yes, i can rename them "manually" but i would take a looong time !

I think that I have ~ 190 variables something like that ahaha

There are like :

a_q1_i1_nbrep

a_q1_i1_rests

a_q1_i1_value

...

a_q1_i2_nbrep

....

b_q1_.....

....

So if you have a piece of code, i would be very happy haha

Thanks again,

Mateo

[PeterClemmensen · Accepted solution]

Ok. There may be a smarter way, but this works. 

data have ;
format a_1 a_2 a_3 b_1 b_2 d c $2. ;
input a_1 a_2 a_3 b_1 b_2 d c ;
cards;
a1 a2 a3 b1 b2 d c
a1 a2 a3 b1 b2 d c
;

proc sql noprint;
    select name into :bvars separated by ' '
    from dictionary.columns
    where libname='WORK' & memname='HAVE' & name like 'b%';
    select name into :avars separated by ' '
    from dictionary.columns
    where libname='WORK' & memname='HAVE' & name like 'a%';
quit;

%put &bvars.;
%put &avars.;

Data want ;
retain d &bvars. &avars. ;
set have ;
run ;

[Onizuka]

Thanks to your idea, I was currently trying to do the same thing by going through the dictionary.columns but you have been much faster than me! haha

I try this and I come back after 😃

In any case, thank you very much!

[PeterClemmensen]

No problem, let me know if it works for you 🙂

[Onizuka]

It seems to work ! Thank you for teaching me this 🙂

Mateo

[FreelanceReinh]

Hello @Onizuka,

An alternative approach uses a "non-executable" SET statement in conjunction with selected KEEP= dataset options (where you can use variable lists):

data want;
retain d;
if 0 then set have(keep=b:) have(keep=a:);
set have;
run;

[Onizuka]

---

@FreelanceReinh wrote:

Hello @Onizuka,

An alternative approach uses a "non-executable" SET statement in conjunction with selected KEEP= dataset options (where you can use variable lists):

data want;
retain d;
if 0 then set have(keep=b:) have(keep=a:);
set have;
run;

---

Hello @FreelanceReinh , thank you for your answer, I will try your solution too, thanks again ! 🙂