BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vanmon1
Obsidian | Level 7

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;



1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

 
 

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
vanmon1
Obsidian | Level 7

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;
Haikuo
Onyx | Level 15

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;

 
 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 771 views
  • 1 like
  • 3 in conversation