creating new columns from a column

[lalohg]

Hi there, 

I have column 1 in the attached file and wonder if you could help me with the SAS code to create columns 2 to 5 from column 1? column 2 will have the first code, column 3 the second and so on....

Thanks

[Reeza]

1. Use COUNTW() to count the number of words.
2. Use a do loop with SCAN() to parse the items.

[novinosrin]

are the length of each value consistently <=4?

[lalohg · Accepted solution]

Yes!

[novinosrin]

data have;
input col1 & $30.; 
cards;
E230
A450
C560 D540 
E34
B45 C30
K568
J340 A235 C345 Z340
H45
;
data want;
set have;
array new_col(5)$4;
do _n_=1 to countw(col1);
new_col(_n_)=scan(col1,_n_,' ');
end;
run;

[lalohg]

it worked just fine thanks Mark!!

[CharlotteCain]

@lalohg Not appropriate to mark your own response. You should choose one of the two solutions you got and give them the credit!

[lalohg]

Hi novinosrin,

I am having problems to get new columns. Like col1 in the code you sent me I have 4 more columns from which I would like to separate the codes, the names of all columns are: 

codcau21

codcau21
codcau31
codcau41
codcau51

When I run the code below:

data test2;
set Def2009_2017; /*(this data set has more than six million rows)*/
array disease1(5)$4; do _n_=1 to countw(codcau21); disease1(_n_)=scan(codcau21,_n_,' ');end;
array disease2(5)$4; do _n_=1 to countw(codcau21); disease2(_n_)=scan(codcau21,_n_,' ');end;
array disease3(5)$4; do _n_=1 to countw(codcau31); disease3(_n_)=scan(codcau31,_n_,' ');end;
array disease4(5)$4; do _n_=1 to countw(codcau41); disease4(_n_)=scan(codcau41,_n_,' ');end;
array disease5(5)$4; do _n_=1 to countw(codcau51); disease5(_n_)=scan(codcau51,_n_,' ');end;
run;

I get the following error message in the log window:

49 data test2;
50 set def.def2009_2017;
51 array disease1(5)$4; do _n_=1 to countw(codcau21); disease1(_n_)=scan(codcau21,_n_,' ');end;
52 array disease2(5)$4; do _n_=1 to countw(codcau21); disease2(_n_)=scan(codcau21,_n_,' ');end;
53 array disease3(5)$4; do _n_=1 to countw(codcau31); disease3(_n_)=scan(codcau31,_n_,' ');end;
54 array disease4(5)$4; do _n_=1 to countw(codcau41); disease4(_n_)=scan(codcau41,_n_,' ');end;
55 array disease5(5)$4; do _n_=1 to countw(codcau51); disease5(_n_)=scan(codcau51,_n_,' ');end;
56 run;

ERROR: Array subscript out of range at línea 51 columna 52.
D_R= ent_regis=1 mun_regis=1 ent_resid=1 mun_resid=1 tloc_resid=15 loc_resid=1 ent_ocurr=1
mun_ocurr=1 loc_ocur=1 tloc_ocurr=15 causa_def=X599 lista_mex=51Z sexo=1 edad=4091 dia_ocurr=11
mes_ocurr=12 anio_ocur=2008 dia_regis=14 mes_regis=1 anio_regis=2009 dia_nacim=25 mes_nacim=8
anio_nacim=1917 ocupacion=2 escolarida=7 edo_civil=2 presunto=1 ocurr_trab=2 lugar_ocur=0
necropsia=9 asist_medi=1 sitio_ocur=4 cond_cert=1 nacionalid=1 derechohab=3 embarazo=8 rel_emba=8
horas=9 minutos=20 capitulo=20 grupo=25 lista1=103 gr_lismex=E51 vio_fami=8 area_ur=1 edad_agru=23
complicaro=8 dia_cert=11 mes_cert=12 anio_cert=2008 peso=8888 maternas=
causa11=HEMATOMA SUBDURAL BITEMPORAL CAUSAC= CAUSAD= CAUSAE= codcau11=S065 dura1=2 MESES
causa21=TRAUMATISMO CRANEO ENCEFALICO CAUSA9= CAUSAA= CAUSAB=
codcau21=S069 S069 S069 S069 S099 Y040 dura2=7 DIAS causa31= CAUSA6= CAUSA7= CAUSA8= codcau31=
dura3= causa41= CAUSA3= CAUSA4= CAUSA5= codcau41= dura4=
causa51=HIPERTENSION ARTERIAL SISTEMICA CAUSA0= CAUSA1= CAUSA2= codcau51=I10 W892 dura51=
loc_ocurr=. lengua=. cond_act=. par_agre=. ent_ocules=. mun_ocules=. loc_ocules=. razon_m=.
disease11=S069 disease12=S069 disease13=S069 disease14=S069 disease15=S099 disease21= disease22=
disease23= disease24= disease25= disease31= disease32= disease33= disease34= disease35=
disease41= disease42= disease43= disease44= disease45= disease51= disease52= disease53=
disease54= disease55= _ERROR_=1 _N_=6
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 750 observations read from the data set DEF.DEF2009_2017.
WARNING: The data set WORK.TEST2 may be incomplete. When this step was stopped there were 749
observations and 116 variables.
WARNING: Data set WORK.TEST2 was not replaced because este paso se ha parado.
NOTE: DATA statement used (Total process time):
real time 0.50 segundos
cpu time 0.07 segundos

and wonder what went wrong?

could you please help me to fix this?

thanks

lalohg