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 |
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
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;
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.