usubjid ARMCD ARM
7959011 GPRTNKYKNSLWPCJEHGKG ACCUVE1\ACCUVE2\ACCUVE3\ACCUVE4\ACCUVE5\ACCUVE6\ACCUVE7\ACCUVE8\ACCUVE9\ACCUVE10
I want to split above single record into 10 records and each record should contains subjid, armcd and arm and each record should populate respected ARM and ARMCD accordingly.
By using the delimeter '\' successfully I have spatted ARM variable into 10 records but I am unable to split ARMCD accordingly with below program. Can anyone please help on this.
ARMCD has 20 characters and each 2 chars are belongs to 1 treatment.
GP = ACCUVE1
RT = ACCUVE2
NK = ACCUVE3.....
In final dataset I need
usubjid ARMCD ARM
7959011 GP ACCUVE1
7959011 RT ACCUVE2
7959011 NK ACCUVE3................
Thanks in advance.
MY PROGRAM:
%let p = 0;
data trtp_bi_test;
set t58;
do i = 1 to 10;
length trtpval $200;
trtp = "TRT"||put(i,z2.)||"P";
trtplbl = "Planned Treatment for Period "||put(i,z2.);
trtpval = scan(armf,i,"\");
j=0;
trtpcdval = substr(armcd, i+j, 2);
j+1;
output;
end;
run;
I kept trying to do this in one DATA step, but I had to break it down into two separate ones. I know someone else would know how to do it in one.
data have;
input usubjid 8. armcd :$30. arm :$300.;
infile datalines delimiter = " ";
datalines;
7959011 GPRTNKYKNSLWPCJEHGKG ACCUVE1\ACCUVE2\ACCUVE3\ACCUVE4\ACCUVE5\ACCUVE6\ACCUVE7\ACCUVE8\ACCUVE9\ACCUVE10
;
run;
data armcd (drop = i armcd max_len);
set have (keep = usubjid armcd);
max_len = max(length(armcd));
do i = 1 to length(armcd) by 2 until (i > max_len);
armcd_t = substr(armcd, i, 2);
output;
end;
run;
data arm (drop = i arm);
set have (keep = usubjid arm);
do i = 1 to countw(arm, "\");
arm_t = scan(arm, i, "\");
output;
end;
run;
data want;
merge
armcd (in = a rename = (armcd_t = armcd))
arm (in = b rename = (arm_t = arm));
by usubjid;
if a and b;
run;
Obs usubjid armcd arm 1 7959011 GP ACCUVE1 2 7959011 RT ACCUVE2 3 7959011 NK ACCUVE3 4 7959011 YK ACCUVE4 5 7959011 NS ACCUVE5 6 7959011 LW ACCUVE6 7 7959011 PC ACCUVE7 8 7959011 JE ACCUVE8 9 7959011 HG ACCUVE9 10 7959011 KG ACCUVE10
Just use a little arithmetic to figure out where each ARMCD starts.
data have;
input usubjid armcd :$30. arm :$300.;
datalines;
7959011
GPRTNKYKNSLWPCJEHGKG
ACCUVE1\ACCUVE2\ACCUVE3\ACCUVE4\ACCUVE5\ACCUVE6\ACCUVE7\ACCUVE8\ACCUVE9\ACCUVE10
;
data want;
set have(rename=(armcd=armcd_string arm=arm_string));
length ARMCD $2 ARM $20;
do index=1 to 10 ;
ARMCD=substr(armcd_string,2*(index-1)+1,2);
ARM=scan(arm_string,index,'\');
output;
end;
drop armcd_string arm_string;
run;
Obs usubjid ARMCD ARM index 1 7959011 GP ACCUVE1 1 2 7959011 RT ACCUVE2 2 3 7959011 NK ACCUVE3 3 4 7959011 YK ACCUVE4 4 5 7959011 NS ACCUVE5 5 6 7959011 LW ACCUVE6 6 7 7959011 PC ACCUVE7 7 8 7959011 JE ACCUVE8 8 9 7959011 HG ACCUVE9 9 10 7959011 KG ACCUVE10 10
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.