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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.