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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.