topic Re: Reorder variables with retain, some problem using prefix in SAS Programming

Reorder variables with retain, some problem using prefix

Onizuka — Wed, 17 Jul 2019 09:03:57 GMT

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

Re: Reorder variables with retain, some problem using prefix

PeterClemmensen — Wed, 17 Jul 2019 09:30:03 GMT

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.

Re: Reorder variables with retain, some problem using prefix

Onizuka — Wed, 17 Jul 2019 09:34:54 GMT

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

Re: Reorder variables with retain, some problem using prefix

PeterClemmensen — Wed, 17 Jul 2019 09:41:34 GMT

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 ;

Re: Reorder variables with retain, some problem using prefix

Onizuka — Wed, 17 Jul 2019 09:48:21 GMT

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!

Re: Reorder variables with retain, some problem using prefix

PeterClemmensen — Wed, 17 Jul 2019 09:51:36 GMT

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

Re: Reorder variables with retain, some problem using prefix

Onizuka — Wed, 17 Jul 2019 10:01:41 GMT

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

Mateo

Re: Reorder variables with retain, some problem using prefix

FreelanceReinh — Wed, 17 Jul 2019 15:57:11 GMT

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;

Re: Reorder variables with retain, some problem using prefix

Onizuka — Thu, 18 Jul 2019 07:34:04 GMT

@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 ! 🙂