topic Re: Array and Transpose based on data values in SAS Programming

Array and Transpose based on data values

raja777pharma — Thu, 30 Jan 2020 10:33:33 GMT

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

Yes

16Apr2019

Yes

16Apr2019

002

VSIST1

Yes

06May2019

Yes

06May2019

Yes

06May2019

002

VSIST2

Yes

13Jun2019

Yes

13Jun2019

Yes

13Jun2019

003

VSIST1

Yes

22Apr2019

Yes

22Apr2019

Yes

22Apr2019

004

VSIST1

Yes

11Sep2019

Yes

11Sep2019

Yes

11Sep2019

004

VSIST2

Yes

05Nov2019

Yes

05Nov2019

Yes

05Nov2019

004

VSIST2

Yes

03Dec2019

Yes

03Dec2019

Yes

03Dec2019

005

VSIST1

Yes

12Dec2019

Yes

12Dec2019

Yes

12Dec2019

006

VSIST1

Yes

30Oct2019

Yes

30Oct2019

Yes

30Oct2019

007

VSIST1

Yes

23Oct2019

Yes

23Oct2019

Yes

23Oct2019

008

VSIST1

Yes

02Jan2020

Yes

02Jan2020

Yes

02Jan2020

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

Re: Array and Transpose based on data values

ed_sas_member — Thu, 30 Jan 2020 12:09:59 GMT

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;

Re: Array and Transpose based on data values

raja777pharma — Thu, 30 Jan 2020 12:18:57 GMT

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.

Re: Array and Transpose based on data values

ed_sas_member — Thu, 30 Jan 2020 12:31:41 GMT

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;