topic Re: how to append n columns into 1 column in a dataset in SAS Programming

how to append n columns into 1 column in a dataset

yoyong555 — Fri, 22 Nov 2019 02:10:01 GMT

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.

Re: how to append n columns into 1 column in a dataset

hashman — Fri, 22 Nov 2019 02:57:55 GMT

@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.

Re: how to append n columns into 1 column in a dataset

KachiM — Fri, 22 Nov 2019 04:17:36 GMT

@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;

Re: how to append n columns into 1 column in a dataset

gamotte — Fri, 22 Nov 2019 09:50:51 GMT

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;

Re: how to append n columns into 1 column in a dataset

Ksharp — Fri, 22 Nov 2019 11:38:47 GMT

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;

Re: how to append n columns into 1 column in a dataset

yoyong555 — Sun, 24 Nov 2019 20:51:36 GMT

Thank you @hashman.