I have a table which looks like the one below. I also have another table which has "Q"s that went through a filter. What I want to do is to replace the names of the"Q"s that are as rows in the second table with the ones that match with the Q's that are the column names in te first table and also type the matching ones as '_Q' or 'Q_' . Actually ı could achive the process transposing the table three times. What I want to know is whether there is another shorter way of doing this. Thank you. data table;
length ID $ 7 Q1 8 Q2 8 Q3 8 Q4 8 Q5 8 Q6 8;
infile datalines missover dlm=",";
input ID Q1 Q2 Q3 Q4 Q5 Q6 ;
datalines;
RefID1,0.90,0.80,0.00,0.90,0.00,0.70
RefID2,0.100,0.100,0.00,0.70,0.00,60
RefID3,0.40,0.80,0.00,0.90,0.00,0.50
RefID4,0.55,0.80,0.05,0.90,0.00,0.69
RefID5,0.00,0.80,0.60,0.90,0.20,0.90
RefID6,0.96,0.00,0.40,0.90,0.00,0.95
RefID7,0.00,0.80,0.90,0.90,0.00,0.99
RefID8,0.56,0.80,0.55,0.90,0.00,0.93
RefID9,0.99,0.80,0.99,0.90,0.00,0.70
RefID10,0.89,0.88,0.56,0.90,0.00,0.00
;
%let colCnt=6;
data out_data(keep=Percentage Variable);
format pcntage percent7.2;
array t t1-t&colCnt.;
retain t1-t&colCnt. 0; * retain some total counts;
set table end = eof;
array Q Q1-Q&colCnt.;
do i=1 to &colcnt;
t(i)=t(i)+(Q(i)ne 0); * add each non-missing to the total count ;
end;
if eof then do i=1 to &colcnt.;
Variable='Q'||strip(put(i,best.)); * create column name ;
Percentage=t(i)/_n_; * calculate %age ;
if Percentage>0.70 then output; * output one row for each of the analyzed columns;
end;
run;
PROC TRANSPOSE DATA=WORK.table
OUT=WORK.TransposedTABLE
PREFIX=Q
NAME=QKolon ;
VAR Q:;
RUN;
QUIT;
PROC SQL;
create table tableJoin2 as
select *, Case
When j.Variable =t.QKolon then left(trim(j.Variable)||"_")
else t.QKolon
end as newColumn
from TransposedTABLE as T
left join out_data as J
on(J.Variable=T.QKolon);
quit;
PROC TRANSPOSE DATA=WORK.TABLEJOIN2
OUT=WORK.TABLEJOIN3(drop=_:) ;
ID newColumn;
VAR Q1-Q10;
RUN;
QUIT;
... View more
