aggregate by column and paste row values together

[ngnikhilgoyal]

Have:

data test;

   input a b c d e f g h i j k l;

   datalines;

1 0 0 0 0 1 0 0 1 0 0 0

0 1 0 0 0 0 1 0 0 1 0 0

0 0 1 0 1 0 0 0 1 0 0 0

0 0 0 1 0 0 0 1 0 0 1 0

;

run;

WANT:

block	col1	col2	col3	col4
1	1000	0100	0010	0001
2	0100	0010	1000	0001
3	1000	0100	1000	0010

Here's what I tried:

proc transpose data = have out = test_t;

run;

data test_t;

set test_t;

retain block;

if _n_ = 1 then block = 1;

if mod(_n_/4,1) = 0.25 and _n_ gt 1 then block +1;

run;

This gives me:

Name	Col1	Col2	Col3	Col4	block
a	1	0	0	0	1
b	0	1	0	0	1
c	0	0	1	0	1
d	0	0	0	1	1
e	0	0	1	0	2
f	1	0	0	0	2
g	0	1	0	0	2
h	0	0	0	1	2
i	1	0	1	0	3
j	0	1	0	0	3
k	0	0	0	1	3
l	0	0	0	0	3

Is there a way I can group this / aggregate by "block" to get something like :

block	col1	col2	col3	col4
1	1000	0100	0010	0001
2	0100	0010	1000	0001
3	1000	0100	1000	0010

[Haikuo]

Here is one way to play:

data test;

input (a b c d e f g h i j k l) (:$1.);

datalines;

1 0 0 0 0 1 0 0 1 0 0 0

0 1 0 0 0 0 1 0 0 1 0 0

0 0 1 0 1 0 0 0 1 0 0 0

0 0 0 1 0 0 0 1 0 0 1 0

;

run;

proc sql;

select CEIL(NVAR/4) into :new trimmed from dictionary.tables where libname='WORK' and memname='TEST';QUIT;

data test1(keep=_:);

set test;

array old(*) a--l;

array new(*) $ 4 _1-_&new.;

call pokelong(peekclong(addrlong(old(1)),dim(old)), addrlong(new(1)),dim(old));

run;

proc transpose data = test1 out = want(rename=(_name_=Block));

var _:;

run;

[PGStats]

You were close :

data test;

   input a b c d e f g h i j k l;

   datalines;

1 0 0 0 0 1 0 0 1 0 0 0

0 1 0 0 0 0 1 0 0 1 0 0

0 0 1 0 1 0 0 0 1 0 0 0

0 0 0 1 0 0 0 1 0 0 1 0

;

proc transpose data=test out=testt(drop=_:); run;

data want;

block + 1;

array f{4} $8;

do i = 1 to dim(f);

    set testt;

    f{i} = cats(of col:);

    end;

output;

keep block f:;

run;

proc print data=want noobs; run;

PG

[ngnikhilgoyal]

Hi

For some reason some of your output values in the want table are not consistent with the input table.

block	f1	f2	f3	f4
1	1000	0100	0010	0001
2	0010	1000	0100	0001
3	1010	0100	0001	0000

Correct output:

block	col1	col2	col3	col4
1	1000	0100	0010	0001
2	0100	0010	1000	0001
3	1000	0100	1000	0010

[PGStats · Accepted solution]

Sorry, I misunderstood. Here is:

data test;

   input a b c d e f g h i j k l;

   datalines;

1 0 0 0 0 1 0 0 1 0 0 0

0 1 0 0 0 0 1 0 0 1 0 0

0 0 1 0 1 0 0 0 1 0 0 0

0 0 0 1 0 0 0 1 0 0 1 0

;

data temp;

length bblock $24;

set test;

array block{3} $8;

bblock = cats(of _numeric_);

do bb = 1 to dim(block);

     block{bb}= substr(bblock, 4*bb-3, 4);

     end;

cc + 1;

col = cats("Col", cc);

keep block: col;

run;

proc transpose data=temp out=want name=block;

id col;

var block:;

run;

proc print data=want noobs; run;

PG

[Ksharp]

Same as PG.

data test;
   input a b c d e f g h i j k l;
   datalines;
1 0 0 0 0 1 0 0 1 0 0 0
0 1 0 0 0 0 1 0 0 1 0 0
0 0 1 0 1 0 0 0 1 0 0 0
0 0 0 1 0 0 0 1 0 0 1 0
;
run;

%let dsid=%sysfunc(open(test));
%let nvar=%sysfunc(attrn(&dsid,nvar));
%let dsid=%sysfunc(close(&dsid));
data temp;
 set test;
 array _x{*} _numeric_;
 array _y{*} $ __1-__%eval(&nvar/4); 
 _n=0;
 do _n_=1 to dim(_x) by 4; 
  _n+1;
  _y(_n)=cats(_x{_n_},_x{_n_+1},_x{_n_+2},_x{_n_+3});
 end;
 keep __:;
run;
proc transpose data=temp out=want;
var _all_;
run;

Xia Keshan