Hi, I'm trying to create 46 new variables from one variable using substr in one dataset and I can only manage to get 46 different datasets with a Macro. i would like to do it more efficiently and also hope I can appply it to repeat a process within proc sql (last bit of code) to avoid all the programming. Thanks. /*This creates 46 datasets*/
%MACRO DO_BINAR;
%DO I = 1 %TO 46 ;
Data test&I;
set tx1;
N&I=substr(CT,&I,1) ;
run;
%END;
%MEND DO_BINAR;
%DO_BINAR;
/*This creates what I need but it takes a lot of typing*/
proc sql;
create table Tel2 as select Codebaliza, Telegram,
substr(CT,1,1) as N0 ,
substr(CT,2,1) as N1 ,
substr(CT,3,1) as N2 ,
substr(CT,4,1) as N3 ,
substr(CT,5,1) as N4,
substr(CT,6,1) as N5,
substr(CT,7,1) as N6,
substr(CT,8,1) as N7,
substr(CT,9,1) as N8,
substr(CT,10,1) as N9,
substr(CT,11,1) as N10,
substr(CT,12,1) as N11,
substr(CT,13,1) as N12,
substr(CT,14,1) as N13,
substr(CT,15,1) as N14,
substr(CT,16,1) as N15,
substr(CT,17,1) as N16,
substr(CT,18,1) as N17,
substr(CT,19,1) as N18,
substr(CT,20,1) as N19,
substr(CT,21,1) as N20,
substr(CT,22,1) as N21,
substr(CT,23,1) as N22,
substr(CT,24,1) as N23,
substr(CT,25,1) as N24,
substr(CT,26,1) as N25,
substr(CT,27,1) as N26,
substr(CT,28,1) as N27,
substr(CT,29,1) as N28,
substr(CT,30,1) as N29,
substr(CT,31,1) as N30,
substr(CT,32,1) as N31,
substr(CT,33,1) as N32,
substr(CT,34,1) as N33,
substr(CT,35,1) as N34,
substr(CT,36,1) as N35,
substr(CT,37,1) as N36,
substr(CT,38,1) as N37,
substr(CT,39,1) as N38,
substr(CT,40,1) as N39,
substr(CT,41,1) as N40,
substr(CT,42,1) as N41,
substr(CT,43,1) as N42,
substr(CT,44,1) as N43,
substr(CT,45,1) as N44,
substr(CT,46,1) as N45
from Tx1;
quit; /*Second task*/ proc sql; create table Tel3 as select a.Codebaliza, a.Telegram,T0.N0_,T1.N1_, T2.N2_, T3.N3_,T4.N4_,T5.N5_, T6.N6_, T7.N7_,T8.N8_,T9.N9_,T10.N10_,T11.N11_,T12.N12_,T13.N13_, T14.N14_, T15.N15_, T16.N16_, T17.N17_, T18.N18_, T19.N19_, T20.N20_, T21.N21_, T22.N22_,T23.N23_,T24.N24_,T25.N25_,T26.N26_,T27.N27_,T28.N28_,T29.N29_, T30.N30_,T31.N31_,T32.N32_,T33.N33_,T34.N34_,T35.N35_,T36.N36_,T37.N37_,T38.N38_,T39.N39_,T40.N40_,T41.N41_,T42.N42_,T43.N43_,T44.N44_,T45.N45_ from Tel2 a left join (select codebaliza, case when a.N0=Hexa then b.Binar else a.N0 end as N0_ from Tel2 a left join HB b on a.N0=b.Hexa) T0 on a.codebaliza=T0.codebaliza left join (select codebaliza, case when a.N1=Hexa then b.Binar else a.N1 end as N1_ from Tel2 a left join HB b on a.N1=b.Hexa) T1 on a.codebaliza=T1.codebaliza left join (select codebaliza, case when a.N2=Hexa then b.Binar else a.N2 end as N2_ from Tel2 a left join HB b on a.N2=b.Hexa) T2 on a.codebaliza=T2.codebaliza left join (select codebaliza, case when a.N3=Hexa then b.Binar else a.N3 end as N3_ from Tel2 a left join HB b on a.N3=b.Hexa) T3 on a.codebaliza=T3.codebaliza left join (select codebaliza, case when a.N4=Hexa then b.Binar else a.N4 end as N4_ from Tel2 a left join HB b on a.N4=b.Hexa) T4 on a.codebaliza=T4.codebaliza left join (select codebaliza, case when a.N5=Hexa then b.Binar else a.N5 end as N5_ from Tel2 a left join HB b on a.N5=b.Hexa) T5 on a.codebaliza=T5.codebaliza left join (select codebaliza, case when a.N6=Hexa then b.Binar else a.N6 end as N6_ from Tel2 a left join HB b on a.N6=b.Hexa) T6 on a.codebaliza=T6.codebaliza left join (select codebaliza, case when a.N7=Hexa then b.Binar else a.N7 end as N7_ from Tel2 a left join HB b on a.N7=b.Hexa) T7 on a.codebaliza=T7.codebaliza left join (select codebaliza, case when a.N8=Hexa then b.Binar else a.N8 end as N8_ from Tel2 a left join HB b on a.N8=b.Hexa) T8 on a.codebaliza=T8.codebaliza left join (select codebaliza, case when a.N9=Hexa then b.Binar else a.N9 end as N9_ from Tel2 a left join HB b on a.N9=b.Hexa) T9 on a.codebaliza=T9.codebaliza left join (select codebaliza, case when a.N10=Hexa then b.Binar else a.N10 end as N10_ from Tel2 a left join HB b on a.N10=b.Hexa) T10 on a.codebaliza=T10.codebaliza left join (select codebaliza, case when a.N11=Hexa then b.Binar else a.N11 end as N11_ from Tel2 a left join HB b on a.N11=b.Hexa) T11 on a.codebaliza=T11.codebaliza left join (select codebaliza, case when a.N12=Hexa then b.Binar else a.N12 end as N12_ from Tel2 a left join HB b on a.N12=b.Hexa) T12 on a.codebaliza=T12.codebaliza left join (select codebaliza, case when a.N13=Hexa then b.Binar else a.N13 end as N13_ from Tel2 a left join HB b on a.N13=b.Hexa) T13 on a.codebaliza=T13.codebaliza left join (select codebaliza, case when a.N14=Hexa then b.Binar else a.N14 end as N14_ from Tel2 a left join HB b on a.N14=b.Hexa) T14 on a.codebaliza=T14.codebaliza left join (select codebaliza, case when a.N15=Hexa then b.Binar else a.N15 end as N15_ from Tel2 a left join HB b on a.N15=b.Hexa) T15 on a.codebaliza=T15.codebaliza left join (select codebaliza, case when a.N16=Hexa then b.Binar else a.N16 end as N16_ from Tel2 a left join HB b on a.N16=b.Hexa) T16 on a.codebaliza=T16.codebaliza left join (select codebaliza, case when a.N17=Hexa then b.Binar else a.N17 end as N17_ from Tel2 a left join HB b on a.N17=b.Hexa) T17 on a.codebaliza=T17.codebaliza left join (select codebaliza, case when a.N18=Hexa then b.Binar else a.N18 end as N18_ from Tel2 a left join HB b on a.N18=b.Hexa) T18 on a.codebaliza=T18.codebaliza left join (select codebaliza, case when a.N19=Hexa then b.Binar else a.N19 end as N19_ from Tel2 a left join HB b on a.N19=b.Hexa) T19 on a.codebaliza=T19.codebaliza left join (select codebaliza, case when a.N20=Hexa then b.Binar else a.N20 end as N20_ from Tel2 a left join HB b on a.N20=b.Hexa) T20 on a.codebaliza=T20.codebaliza left join (select codebaliza, case when a.N21=Hexa then b.Binar else a.N21 end as N21_ from Tel2 a left join HB b on a.N21=b.Hexa) T21 on a.codebaliza=T21.codebaliza left join (select codebaliza, case when a.N22=Hexa then b.Binar else a.N22 end as N22_ from Tel2 a left join HB b on a.N22=b.Hexa) T22 on a.codebaliza=T22.codebaliza left join (select codebaliza, case when a.N23=Hexa then b.Binar else a.N23 end as N23_ from Tel2 a left join HB b on a.N23=b.Hexa) T23 on a.codebaliza=T23.codebaliza left join (select codebaliza, case when a.N24=Hexa then b.Binar else a.N24 end as N24_ from Tel2 a left join HB b on a.N24=b.Hexa) T24 on a.codebaliza=T24.codebaliza left join (select codebaliza, case when a.N25=Hexa then b.Binar else a.N25 end as N25_ from Tel2 a left join HB b on a.N25=b.Hexa) T25 on a.codebaliza=T25.codebaliza left join (select codebaliza, case when a.N26=Hexa then b.Binar else a.N26 end as N26_ from Tel2 a left join HB b on a.N26=b.Hexa) T26 on a.codebaliza=T26.codebaliza left join (select codebaliza, case when a.N27=Hexa then b.Binar else a.N27 end as N27_ from Tel2 a left join HB b on a.N27=b.Hexa) T27 on a.codebaliza=T27.codebaliza left join (select codebaliza, case when a.N28=Hexa then b.Binar else a.N28 end as N28_ from Tel2 a left join HB b on a.N28=b.Hexa) T28 on a.codebaliza=T28.codebaliza left join (select codebaliza, case when a.N29=Hexa then b.Binar else a.N29 end as N29_ from Tel2 a left join HB b on a.N29=b.Hexa) T29 on a.codebaliza=T29.codebaliza left join (select codebaliza, case when a.N30=Hexa then b.Binar else a.N30 end as N30_ from Tel2 a left join HB b on a.N30=b.Hexa) T30 on a.codebaliza=T30.codebaliza left join (select codebaliza, case when a.N31=Hexa then b.Binar else a.N31 end as N31_ from Tel2 a left join HB b on a.N31=b.Hexa) T31 on a.codebaliza=T31.codebaliza left join (select codebaliza, case when a.N32=Hexa then b.Binar else a.N32 end as N32_ from Tel2 a left join HB b on a.N32=b.Hexa) T32 on a.codebaliza=T32.codebaliza left join (select codebaliza, case when a.N33=Hexa then b.Binar else a.N33 end as N33_ from Tel2 a left join HB b on a.N33=b.Hexa) T33 on a.codebaliza=T33.codebaliza left join (select codebaliza, case when a.N34=Hexa then b.Binar else a.N34 end as N34_ from Tel2 a left join HB b on a.N34=b.Hexa) T34 on a.codebaliza=T34.codebaliza left join (select codebaliza, case when a.N35=Hexa then b.Binar else a.N35 end as N35_ from Tel2 a left join HB b on a.N35=b.Hexa) T35 on a.codebaliza=T35.codebaliza left join (select codebaliza, case when a.N36=Hexa then b.Binar else a.N36 end as N36_ from Tel2 a left join HB b on a.N36=b.Hexa) T36 on a.codebaliza=T36.codebaliza left join (select codebaliza, case when a.N37=Hexa then b.Binar else a.N37 end as N37_ from Tel2 a left join HB b on a.N37=b.Hexa) T37 on a.codebaliza=T37.codebaliza left join (select codebaliza, case when a.N38=Hexa then b.Binar else a.N38 end as N38_ from Tel2 a left join HB b on a.N38=b.Hexa) T38 on a.codebaliza=T38.codebaliza left join (select codebaliza, case when a.N39=Hexa then b.Binar else a.N39 end as N39_ from Tel2 a left join HB b on a.N39=b.Hexa) T39 on a.codebaliza=T39.codebaliza left join (select codebaliza, case when a.N40=Hexa then b.Binar else a.N40 end as N40_ from Tel2 a left join HB b on a.N40=b.Hexa) T40 on a.codebaliza=T40.codebaliza left join (select codebaliza, case when a.N41=Hexa then b.Binar else a.N41 end as N41_ from Tel2 a left join HB b on a.N41=b.Hexa) T41 on a.codebaliza=T41.codebaliza left join (select codebaliza, case when a.N42=Hexa then b.Binar else a.N42 end as N42_ from Tel2 a left join HB b on a.N42=b.Hexa) T42 on a.codebaliza=T42.codebaliza left join (select codebaliza, case when a.N43=Hexa then b.Binar else a.N43 end as N43_ from Tel2 a left join HB b on a.N43=b.Hexa) T43 on a.codebaliza=T43.codebaliza left join (select codebaliza, case when a.N44=Hexa then b.Binar else a.N44 end as N44_ from Tel2 a left join HB b on a.N44=b.Hexa) T44 on a.codebaliza=T44.codebaliza left join (select codebaliza, case when a.N45=Hexa then b.Binar else a.N45 end as N45_ from Tel2 a left join HB b on a.N45=b.Hexa) T45 on a.codebaliza=T45.codebaliza; quit;
... View more