Help using Base SAS procedures

aggregate by column and paste row values together

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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:

blockcol1col2col3col4
11000010000100001
20100001010000001
31000010010000010

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:

NameCol1Col2Col3Col4block
a10001
b01001
c00101
d00011
e00102
f10002
g01002
h00012
i10103
j01003
k00013
l00003

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

blockcol1col2col3col4
11000010000100001
20100001010000001
31000010010000010

Accepted Solutions
Solution
‎04-20-2015 05:19 PM
Respected Advisor
Posts: 4,919

Re: aggregate by column and paste row values together

Posted in reply to ngnikhilgoyal

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: aggregate by column and paste row values together

Posted in reply to ngnikhilgoyal

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=_Smiley Happy;

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;

Respected Advisor
Posts: 4,919

Re: aggregate by column and paste row values together

Posted in reply to ngnikhilgoyal

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=_Smiley Happy; run;

data want;

block + 1;

array f{4} $8;

do i = 1 to dim(f);

    set testt;

    f{i} = cats(of colSmiley Happy;

    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.

blockf1f2f3f4
11000010000100001
20010100001000001
31010010000010000

Correct output:

blockcol1col2col3col4
11000010000100001
20100001010000001
31000010010000010
Solution
‎04-20-2015 05:19 PM
Respected Advisor
Posts: 4,919

Re: aggregate by column and paste row values together

Posted in reply to ngnikhilgoyal

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,020

Re: aggregate by column and paste row values together

Posted in reply to ngnikhilgoyal

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 255 views
  • 6 likes
  • 4 in conversation