Solved
Contributor
Posts: 40

# aggregate by column and paste row values together

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

Accepted Solutions
Solution
‎04-20-2015 05:19 PM
Posts: 5,541

## Re: aggregate by column and paste row values together

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

PG

All Replies
Posts: 3,167

## Re: aggregate by column and paste row values together

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

run;

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

var _:;

run;

Posts: 5,541

## Re: aggregate by column and paste row values together

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

PG
Contributor
Posts: 40

## Re: aggregate by column and paste row values together

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
Solution
‎04-20-2015 05:19 PM
Posts: 5,541

## Re: aggregate by column and paste row values together

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

PG
Super User
Posts: 10,787

## Re: aggregate by column and paste row values together

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

🔒 This topic is solved and locked.