BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

data_null__
Jade | Level 19

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;

3-13-2015 6-32-09 AM.png
CharlotteCain
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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

CharlotteCain
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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;

3-13-2015 8-20-30 AM.png
Astounding
PROC Star

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.

Ksharp
Super User

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

Haikuo
Onyx | Level 15

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

data_null__
Jade | Level 19

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_.
Haikuo
Onyx | Level 15

Thanks, John. Learn some!

CharlotteCain
Quartz | Level 8

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" Smiley Happy 4. John-Data _null_.

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

Good night from Liverpool England,

Charlotte

FriedEgg
SAS Employee

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;

             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;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 15 replies
    • 1551 views
    • 14 likes
    • 7 in conversation