topic Re: How to append SAS Variables "efficiently"? in SAS Programming

How to append SAS Variables "efficiently"?

CharlotteCain — Fri, 13 Mar 2015 10:39:04 GMT

How to append SAS Variables in the sample dataset below: declaring array, transposing and transposing back to one seems to work, but seems tedious and inefficient..Any efficent method plz?

Have:/*what if there are too many variables? hence need efficiency*/

var1 var2 var3 var4

a f k p

b g l q

c h m r

d i n s

e j o t

Want:

var_final index

a 1

b 1

c 1

d 1

e 1

f 2

g 2

h 2

i 2

j 2

k 3

l 3

m 3

n 3

o 3

Re: How to append SAS Variables "efficiently"?

RW9 — Fri, 13 Mar 2015 11:01:25 GMT

Hi,

Well, for a flexible way (i.e. unknown number of vars):

data have;

var1='a'; var2="f"; var3="k"; var4="p"; output;

var1="b"; var2="g"; var3="l"; var4="q"; output;

run;

proc sql noprint;

select NAME

into :V_LIST separated by " "

from SASHELP.VCOLUMN

where LIBNAME="WORK"

and MEMNAME="HAVE";

quit;

data want (keep=var_final index);

set have;

array var{*} &V_LIST.;

do i=1 to dim(var);

var_final=var{i};

index=i;

output;

end;

run;

proc sort data=want;

by index var_final;

run;

Re: How to append SAS Variables "efficiently"?

data_null__ — Fri, 13 Mar 2015 11:33:29 GMT

You only need one PROC TRANSPOSE and a data step view to create INDEX. You don't specify but I assume your VARs are all the same data type. With PROC TRANSPOSE the type could be mixed but the variables are all converted to character in that scenario. This conversion can be very useful, as the numeric's are converted using their associated formats (think VVALUE), I use it often and is something that data step array solutions will struggle with (at least require more code).

data vars;
   length var1-var4 $1; 
   input var1-var4;
   cards; 
a f k p
b g l q
c h m r
d i n s
e j o t
;;;;
   run; 
proc print; 
   run; 
data varsv / view=varsv;
   set vars;
   index = _n_;
   run; 
proc transpose data=varsv out=vars2(drop=_name_  /*maybe not drop*/ rename=(col1=var_final));
   by index;
   var _character_; /*or other SAS Variable List */
   run; 
proc print; 
   run;

Re: How to append SAS Variables "efficiently"?

CharlotteCain — Fri, 13 Mar 2015 12:49:49 GMT

Hi John and Robert, Thank you for the responses. I ran both of your codes, however I am afraid neither of the results are appending the variables as wanted one below the other, rather it's appending the values of variables by the observation.

If you agree with me, your results are the same as what the below code produces

data vars;

length var1-var4 $1;

input var1-var4;

cards;

a f k p

b g l q

c h m r

d i n s

e j o t

;;;;

data want;

set vars;

do i=var1,var2,var3,var4;

index=_n_;

output;

end;

keep i index;

run;

Please accept my apologies if I didn't explain well enough while you guys are offering great help. Basically, I need to append columns one below the other as is i.e even the last sort step in RW9's code will not meet the requirement because the values need not be a,b, c, d based sequence or any other predictable sequence. In other words, its just an append, however the challenge is that there are too many variables. Hence the scalability of the code also matters big time.

Sincerely,

Charlotte

Re: How to append SAS Variables "efficiently"?

RW9 — Fri, 13 Mar 2015 12:55:26 GMT

Hi,

Just don't do the final sort then, I only put that in to match the order given.

Re: How to append SAS Variables "efficiently"?

Ksharp — Fri, 13 Mar 2015 13:02:15 GMT

Alternative way :

data have; 
  var1='a'; var2="f"; var3="k"; var4="p"; output;
  var1="b"; var2="g"; var3="l"; var4="q"; output;
run;
data _null_;
 set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='HAVE'));
 n+1;
 call execute('data temp;set');
 call execute(catt('have(keep=',name,' rename=(',name,'=var_final));index=',n,';run;proc append base=want data=temp force;run;'));
run;

Xia Keshan

Re: How to append SAS Variables "efficiently"?

CharlotteCain — Fri, 13 Mar 2015 13:07:15 GMT

Well Sir, Then it goes back to square as your result will also join as DN's and mine:

a 1

f 2

k 3

p 4

b 1

g 2

l 3

q 4

which unfortunately doesn't meet the wanted output.

Re: How to append SAS Variables "efficiently"?

data_null__ — Fri, 13 Mar 2015 13:21:53 GMT

I see I did miss that detail. You could gen some code and use SET. I'm not sure the limit of the number of data sets that can be SET petty big so it should work with lots of variables. As written using rename the variables must be all the same type.

data vars;
   length var1-var4 $1; 
   input var1-var4;
   cards; 
a f k p
b g l q
c h m r
d i n s
e j o t
;;;;
   run; 
proc print; 
   run; 
proc transpose data=vars(obs=0) out=names;
   var var:;
   run; 
filename FT61F001 temp; 
data _null_; 
   file FT61F001;
   set names end=eof;
   if _n_ eq 1 then put 'Set'; 
   length in $8; 
   in = cats('_in',_n_);
   put +3 'vars(' in= 'keep=' _name_ 'Rename=(' _name_ +(-1) '=final))'; 
   if eof then put +3 ';'; 
   run; 

data appended;
   %include FT61F001 / source2;
   index = findc(cats(of _in:),'1');
   run; 
proc print; 
   run;

Re: How to append SAS Variables "efficiently"?

RW9 — Fri, 13 Mar 2015 13:33:57 GMT

There are solutions above.

I am interested in the purpose of this though. If, as you say there is no logical arrangement of data other than a position in a dataset, how do you intend to process that further, or cover the fact that the position will most likely change? To my mind sorts are used so frequently purely to arrange the data in a logical manner. I can understand that if you have a requirement to have Cat F above Cat C which itself is above Cat B then the data gives no logical order, but in those instances I would be assigning sort variables to be used behind the scenes and not dsiplayed, i.e.

SEQ1 TEXT

1 CAT F

2 CAT C

3 CAT F

That way I can always get the sort I intend.

Re: How to append SAS Variables "efficiently"?

Astounding — Fri, 13 Mar 2015 13:39:18 GMT

Charlotte,

The approach you listed (as well as the RW9 approach) should work fine, but you need to change the sorted order. Just remove one variable name from the final sort, and sort BY INDEX;

That should give you the proper order.

Good luck.

Re: How to append SAS Variables "efficiently"?

Haikuo — Fri, 13 Mar 2015 14:14:11 GMT

A little late for the party. If your RAM is large enough to hold the whole target table, then Hash may meet your requirement of 'efficiency'. Please note, I have reshuffled the order in your input to make sure the sort is working ONLY on the Index:

data vars;

length var1-var4 $1;

input var1-var4;

cards;

c h m r

a f k p

e j o t

b g l q

d i n s

;;;;

data _null_;

if _n_=1 then

do;

declare hash h(multidata:'y', ordered:'y');

h.definekey('index');

h.definedata('_var', 'index');

h.definedone();

end;

set vars end=last;

array var var:;

do over var;

_var=var;

index=(_i_);

rc=h.add();

end;

if last then

rc=h.output(dataset:'want');

run;

Good Luck,

Haikuo

Re: How to append SAS Variables "efficiently"?

data_null__ — Fri, 13 Mar 2015 14:41:56 GMT

You can declare the index for your implicit array so you don't have to assign INDEX=_I_;

array var(index) var:;

DO OVER VAR; will increment INDEX instead of _I_.

Re: How to append SAS Variables "efficiently"?

Haikuo — Fri, 13 Mar 2015 14:47:18 GMT

Thanks, John. Learn some!

Re: How to append SAS Variables "efficiently"?

CharlotteCain — Fri, 13 Mar 2015 17:24:12 GMT

A True Class act!, Brilliant. Late to the party, Well, but first to open the champagne bottle. Yes , thanks to our healthy budget we have great RAM capacity. Thanks everyone, 1.genius and good friend- Xia 2. RW9 3. Robert Virgile-Astounding "I have bought a copy of your macro magic book" 4. John-Data _null_.

Thanks for making my evening a pleasant one indeed!!!.

Good night from Liverpool England,

Charlotte

Re: How to append SAS Variables "efficiently"?

FriedEgg — Fri, 13 Mar 2015 20:43:18 GMT

I have not tested with multiple threads. A single thread for DS2 will be slightly less efficient than data step, but with a large enough problem the multi-thread should provide performance lift in this problem.

data foo;

drop _:;

array var[500];

do _i=1 to 1000000;

do _j=1 to dim(var);

var[_j]=_j;

end;

output;

end;

run;

proc ds2;

thread transpose / overwrite=yes;

declare double index _var;

vararray double var

var1-var500;

keep index _var;

method run();

set x.foo;

do index=1 to dim(var);

_var=var[index];

output;

end;

endthread;

run;

data bar(overwrite=yes);

dcl thread transpose tranp;

method run();

set from tranp threads=1;

end;

enddata;

run;

quit;

data bar2;

keep index _var;

set foo;

array var(index) var:;

do over var;

_var=var;

output;

end;

run;

Re: How to append SAS Variables "efficiently"?

CharlotteCain — Mon, 16 Mar 2015 16:57:18 GMT

Hi Matt, I hope you had a good weekend. Sorry, for the late acknowledgement as I couldn't implement your solution until today at office and as I did today I must confess you are the messiah of SAS. Although, with hope I was eagerly anticipating your response, you seem to marvel me with your awesome "thought and application". Well, I guess Stars are born rather than made. What a code!!Great Performance.

Now, the problem is there aint many Matt's in our office so that's a bit of challenge to even maintain such sophisticated code, but I am so inspired and have welcomed the challenge to learn more. Many Many thanks.

Have a great day,

Charlotte from Liverpool, England