Repeat substr function to create variables in one dataset

vanmon1 — Fri, 23 Feb 2018 12:07:47 GMT

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;

Re: Repeat substr function to create variables in one dataset

PeterClemmensen — Fri, 23 Feb 2018 12:33:51 GMT

If you know that the exact amount of new variables you want to create, then simply do

data _null_;
length CT $46;
CT="qwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklz";
array N{*} $ N1-N46;
do i=1 to length(CT);
   N[i]=substr(CT,i,1);
end;

put _ALL_;
run;

Re: Repeat substr function to create variables in one dataset

vanmon1 — Fri, 23 Feb 2018 12:44:39 GMT

Thank you. My string is alphanumeric and I have 125 lines, records, so I need to do the process of creating 46 variables 125 times.

data want (drop=i);
set tx1;
array N(46) N0-N46;
do i= 1 to 46;
%end;
N[i]=substr(ct,i,1);
%end;
run;

Re: Repeat substr function to create variables in one dataset

Haikuo — Fri, 23 Feb 2018 14:25:25 GMT

This is when Macro language comes in handy. It generates code for you at the spot you want:

%macro _46;
%do i=1 %to 46;
  %if &i<46 %then %do;
  substr(ct,&i,1) as n%eval(&i-1), %end;
  %else  %do; substr(ct,&i,1) as n%eval(&i-1) %end;
 %end;
 %mend;


proc sql; 
create table Tel2 as select Codebaliza, Telegram,
 
%_46
from tx1
;
quit;

topic Re: Repeat substr function to create variables in one dataset in SAS Programming

Repeat substr function to create variables in one dataset

Re: Repeat substr function to create variables in one dataset

Re: Repeat substr function to create variables in one dataset

Re: Repeat substr function to create variables in one dataset