how to append n columns into 1 column in a dataset

[yoyong555]

Hi everyone. 

My data looks something like this (all blanks are missing values):

Location            Count1      Count2          Count3

1                        12                                      7

2                        10                  13                2

3                          5

4                          8                  12               15

And I want the data to look like this:

Location      Count

1                  12

2                  10

3                   5

4                   8

2                  13

4                  12

1                   7

2                   2

4                  15

Thanks for your help.

[hashman]

@yoyong555:

You sample output is (dis?)ordered in a manner, whose pattern I fail to catch. It makes much more sense to me to have it naturally sequenced by location:

data have ;                                                                                                                                                                                                                                                     
  input location count1-count3 ;                                                                                                                                                                                                                                
  cards ;                                                                                                                                                                                                                                                       
1  12   .   7                                                                                                                                                                                                                                                   
2  10  13   2                                                                                                                                                                                                                                                   
3   5   .   .                                                                                                                                                                                                                                                   
4   8  12  15                                                                                                                                                                                                                                                   
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want (drop = count1-count3) ;                                                                                                                                                                                                                              
  set have ;                                                                                                                                                                                                                                                    
  array c count: ;                                                                                                                                                                                                                                              
  do over c ;                                                                                                                                                                                                                                                   
    count = c ;                                                                                                                                                                                                                                                 
    if N (count) then output ;                                                                                                                                                                                                                                  
  end ;                                                                                                                                                                                                                                                         
run ;                           

In which case, you get:

location    count                                                                                                                                                                                                                                               
-----------------                                                                                                                                                                                                                                               
    1         12                                                                                                                                                                                                                                                
    1          7                                                                                                                                                                                                                                                
    2         10                                                                                                                                                                                                                                                
    2         13                                                                                                                                                                                                                                                
    2          2                                                                                                                                                                                                                                                
    3          5                                                                                                                                                                                                                                                
    4          8                                                                                                                                                                                                                                                
    4         12                                                                                                                                                                                                                                                
    4         15 

Kind regards

Paul D. 

[yoyong555]

Thank you @hashman.

[KachiM · Accepted solution]

@yoyong555 

It looks like you want Column-Row order of Count. In another thread yesterday, you have received a solution using Proc Transpose and Proc Sort. Here is an array-based solution.

%let rows = 4;
%let cols = 3;   
data want;
   array k[&rows,&cols] _temporary_;
   do i = 1 by 1 until(eof);
      set have end = eof;
      array c count1 - count&cols;
      do j = 1 to dim(c);
         k[i, j] = c[j];
      end;
   end;
   do j = 1 to dim(c);
      do i = 1 to dim1(k);
         if k[i, j] then do;
            Location = i; 
            count = k[i,j]; 
            output; 
         end;
      end;
   end;
stop;
keep Location count;      
run;

[gamotte]

Hello,

data want;
    set have(keep=location count1 rename=(count1=count))
        have(keep=location count2 rename=(count2=count))
        have(keep=location count3 rename=(count3=count));
    where count ne .;
run;

[Ksharp]

If you have many variables.

data have ;                                                                                                                                                                                                                                                     
  input location count1-count3 ;                                                                                                                                                                                                                                
  cards ;                                                                                                                                                                                                                                                       
1  12   .   7                                                                                                                                                                                                                                                   
2  10  13   2                                                                                                                                                                                                                                                   
3   5   .   .                                                                                                                                                                                                                                                   
4   8  12  15                                                                                                                                                                                                                                                   
;                                                                                                                                                                                                                                                               
run ;  
data temp;
 set have;
 array x{*} count:;
 do i=1 to dim(x);
  if not missing(x{i}) then do; count=x{i};output;end;
 end;
 keep location i count;
run;
proc sort data=temp out=want;
by i;
run;