Array and Transpose based on data values

[raja777pharma]

Hello ,

As per below data table 01 and want to create new data as table 02

if subject have three catergory's TEST,EXTRA and MORE , each in catergory again they three values CT ,MRI and other

if subject had any one of CT , MRI or OTH marked then in New data TYPE is <TEST or EXTRA or MORE or> and TRT is <CT_01 or MRI_02 or OTH_03 >

if subject had mraked two are above then TRT is <multiple>

I want one more data table 03 , as in if any TRT value is multiple then TRT_MULTPLE value will be <TRT_CT_MRI> as shown in below table 03

Table 01 : Exist data

Table 02 : Want new data as per above conditions <New data set 01>

Table 03 : Want new data as per above conditions <new data set 02>

Table 01 :

SUBNUM

VISNAME

TEST_DEC

TESTDAT

TESTCT

TESTMRI

TEST_OTH

EXTRA_DEC

EXTRADAT

EXTRACT

EXTRAMRI

EXTRAOTH

MORE_DEC

MOREDAT

MORECT

MOREMRI

MOREOTH

001

VSIST1

Yes

16Apr2019

X

Yes

16Apr2019

X

Yes

16Apr2019

X

002

VSIST1

Yes

06May2019

X

Yes

06May2019

X

Yes

06May2019

X

002

VSIST2

Yes

13Jun2019

X

Yes

13Jun2019

X

Yes

13Jun2019

X

X

003

VSIST1

Yes

22Apr2019

X

Yes

22Apr2019

X

Yes

22Apr2019

X

X

004

VSIST1

Yes

11Sep2019

X

Yes

11Sep2019

X

Yes

11Sep2019

X

004

VSIST2

Yes

05Nov2019

X

Yes

05Nov2019

X

Yes

05Nov2019

X

004

VSIST2

Yes

03Dec2019

X

Yes

03Dec2019

X

Yes

03Dec2019

X

005

VSIST1

Yes

12Dec2019

X

Yes

12Dec2019

X

Yes

12Dec2019

X

006

VSIST1

Yes

30Oct2019

X

Yes

30Oct2019

X

Yes

30Oct2019

X

X

007

VSIST1

Yes

23Oct2019

X

Yes

23Oct2019

X

Yes

23Oct2019

X

008

VSIST1

Yes

02Jan2020

X

Yes

02Jan2020

X

Yes

02Jan2020

X

Table 02 : New data set need to create as per above data :

SUBNUM	VISNAME	DATE	TYPE	TRT
001	VSIST1	16Apr2019	TEST	CT_01
002	VSIST1	06May2019	TEST	CT_01
002	VSIST2	13Jun2019	TEST	CT_01
003	VSIST1	22Apr2019	TEST	CT_01
004	VSIST1	11Sep2019	TEST	CT_01
004	VSIST2	05Nov2019	TEST	CT_01
004	VSIST2	03Dec2019	TEST	CT_01
005	VSIST1	12Dec2019	TEST	CT_01
006	VSIST1	30Oct2019	TEST	CT_01
007	VSIST1	23Oct2019	TEST	CT_01
008	VSIST1	02Jan2020	TEST	CT_01
001	VSIST1	16Apr2019	EXTRA	MRI_02
002	VSIST1	06May2019	EXTRA	MRI_02
002	VSIST2	13Jun2019	EXTRA	MRI_02
003	VSIST1	22Apr2019	EXTRA	MRI_02
004	VSIST1	11Sep2019	EXTRA	MRI_02
004	VSIST2	05Nov2019	EXTRA	MRI_02
004	VSIST2	03Dec2019	EXTRA	MRI_02
005	VSIST1	12Dec2019	EXTRA	MRI_02
006	VSIST1	30Oct2019	EXTRA	MRI_02
007	VSIST1	23Oct2019	EXTRA	MRI_02
008	VSIST1	02Jan2020	EXTRA	MRI_02
001	VSIST1	16Apr2019	MORE	CT_01
002	VSIST1	06May2019	MORE	CT_01
002	VSIST2	13Jun2019	MORE	MULTIPLE
003	VSIST1	22Apr2019	MORE	MULTIPLE
004	VSIST1	11Sep2019	MORE	CT_01
004	VSIST2	05Nov2019	MORE	CT_01
004	VSIST2	03Dec2019	MORE	CT_01
005	VSIST1	12Dec2019	MORE	CT_01
006	VSIST1	30Oct2019	MORE	MULTIPLE
007	VSIST1	23Oct2019	MORE	CT_01
008	VSIST1	02Jan2020	MORE	CT_01

Table 03 : New data set need to create based on table 01

SUBNUM	VISNAME	DATE	TYPE	TRT_MULTIPLE
001	VSIST1	16Apr2019	TEST	CT_01
002	VSIST1	06May2019	TEST	CT_01
002	VSIST2	13Jun2019	TEST	CT_01
003	VSIST1	22Apr2019	TEST	TRT_CT_MRI
004	VSIST1	11Sep2019	TEST	TRT_CT_MRI
004	VSIST2	05Nov2019	TEST	CT_01
004	VSIST2	03Dec2019	TEST	CT_01
005	VSIST1	12Dec2019	TEST	CT_01
006	VSIST1	30Oct2019	TEST	TRT_CT_OTH
007	VSIST1	23Oct2019	TEST	CT_01
008	VSIST1	02Jan2020	TEST	CT_01

[ed_sas_member]

Hi @raja777pharma 

Hereafter is a piece of code to create table2.

NB: I guess there is a typo in the name of variable "TEST_OTH" -> it should be more logical to have TESTOTH (please rename it so that the macro can work).

I don't understand the logic to create table3 ->could you please explain a bit more? In particular, how should we consider the 'TYPE' variable ? -> it seems that the data you displayed in table 3 correspond to type = "MORE" ?

Best,

data table1;
	infile datalines dlm="09"x dsd missover;
	input SUBNUM VISNAME $
		  TEST_DEC $ TESTDAT:date9. TESTCT $ TESTMRI $ TESTOTH $
		  EXTRA_DEC $ EXTRADAT:date9. EXTRACT $ EXTRAMRI $ EXTRAOTH $
		  MORE_DEC $ MOREDAT:date9. MORECT $ MOREMRI $ MOREOTH $;
	format TESTDAT EXTRADAT MOREDAT date9.;
	datalines;
001	VSIST1	Yes	16Apr2019	X	 	 	Yes	16Apr2019	 	X	 	Yes	16Apr2019	X	 	 
002	VSIST1	Yes	06May2019	X	 	 	Yes	06May2019	 	X	 	Yes	06May2019	X	 	 
002	VSIST2	Yes	13Jun2019	X	 	 	Yes	13Jun2019	 	X	 	Yes	13Jun2019	X	X	 
003	VSIST1	Yes	22Apr2019	X	 	 	Yes	22Apr2019	 	X	 	Yes	22Apr2019	X	X	 
004	VSIST1	Yes	11Sep2019	X	 	 	Yes	11Sep2019	 	X	 	Yes	11Sep2019	X	 	 
004	VSIST2	Yes	05Nov2019	X	 	 	Yes	05Nov2019	 	X	 	Yes	05Nov2019	X	 	 
004	VSIST2	Yes	03Dec2019	X	 	 	Yes	03Dec2019	 	X	 	Yes	03Dec2019	X	 	 
005	VSIST1	Yes	12Dec2019	X	 	 	Yes	12Dec2019	 	X	 	Yes	12Dec2019	X	 	 
006	VSIST1	Yes	30Oct2019	X	 	 	Yes	30Oct2019	 	X	 	Yes	30Oct2019	X	 	X
007	VSIST1	Yes	23Oct2019	X	 	 	Yes	23Oct2019	 	X	 	Yes	23Oct2019	X	 	 
008	VSIST1	Yes	02Jan2020	X	 	 	Yes	02Jan2020	 	X	 	Yes	02Jan2020	X	 	 
;
run;

/* TABLE 2 */

%macro transp (prefix = );
	proc transpose data=table1 out=table1_&prefix (rename=(&prefix.DAT=DATE));
		var &prefix.CT &prefix.MRI &prefix.OTH;
		by SUBNUM VISNAME &prefix.DAT;
	run;
%mend;

%transp(prefix=test);
%transp(prefix=extra);
%transp(prefix=more);

data table1_int;
	set table1_test (in=a) table1_extra (in=b) table1_more (in=c);
	
	length TYPE $ 5 ;
	if 		a then TYPE = "TEST";
	else if b then TYPE = "EXTRA";
	else if c then TYPE = "MORE";
	
	length TRT $ 8;
	if 		prxmatch('/CT/', _name_)  then TRT = "CT_01";
	else if prxmatch('/MRI/', _name_) then TRT = "MRI_02";
	else if prxmatch('/OTH/', _name_) then TRT = "OTH_03";

	if col1 ="X" then output;
	drop col1 _name_;
;
run;

data table2;
	set table1_int;
	by TYPE SUBNUM VISNAME DATE notsorted;
	if first.date then count= 0;
	count + 1;
	if count > 1 then TRT = "MULTIPLE";
	drop count;
	if last.date then output;
run;

[raja777pharma]

Hello ed_sas_memebr ,

Thank you for answer .

for table three purpose is if any TRT value is multiple , then we have to know what are multiple values are checked and out the same value in to another variable , in that means TYPE can value is TRT1-3.

[ed_sas_member]

Hi @raja777pharma 

Thank you for the clarification.

Here is an attempt to create table3. Let me know if does meet your expectations.

Best,

/* TABLE 3 */

%macro macro2 (prefix = );
	data table3_&prefix.;
		set table1;
		length TYPE $ 5;
		TYPE = upcase("&prefix.");
		if &prefix.CT  = "X" then flag_CT = "CT";
		if &prefix.MRI = "X" then flag_MRI = "MRI";
		if &prefix.OTH = "X" then flag_OTH = "OTH";
		TRT_MULTIPLE = tranwrd(catx("_","TRT",flag_CT,flag_MRI,flag_OTH),"_.","");
		if TRT_MULTIPLE = "TRT_CT" then TRT_MULTIPLE = "CT_01";
		if TRT_MULTIPLE = "TRT_MRI" then TRT_MULTIPLE = "MRI_02";
		if TRT_MULTIPLE = "TRT_OTH" then TRT_MULTIPLE = "OTH_03";
		keep SUBNUM	VISNAME	TESTDAT TYPE TRT_MULTIPLE;
	run;

%mend;

%macro2(prefix =TEST);
%macro2(prefix =EXTRA);
%macro2(prefix =MORE);

data table3;
	set table3_test table3_extra table3_more;
run;