topic Re: array or transpose for data in SAS Programming

array or transpose for data

raja777pharma — Mon, 03 Feb 2020 09:48:32 GMT

Hello ,

As per below data table 01 would like to create two data sets (table 02 and 03)

In one data set need to unique values if any location is selected then location_name values will be <var names> like in below table 02 , if any multiple locations marked then location_name have value = MULTIPLE

for another data set need if any location_name have value = MULTIPLE then those multiple location number with chronological order like table 03

Note : Locations number will give chronological order like NY=1 , NJ=2 , CA=3 .....

Table 01:

SUBNUM

VISNAME

TEST_DEC

TESTDAT

001

VSIST1

16Apr2019

002

VSIST1

06May2019

002

VSIST2

13Jun2019

003

VSIST1

22Apr2019

004

VSIST1

11Sep2019

004

VSIST2

05Nov2019

004

VSIST2

03Dec2019

005

VSIST1

12Dec2019

006

VSIST1

30Oct2019

007

VSIST1

23Oct2019

008

VSIST1

02Jan2020

Data set 01/Table 02 :

SUBNUM	VISNAME	TEST_DEC	TESTDAT	LOCATION_NAME	YES/NO
001	VSIST1	YES	16Apr2019	CA	YES
002	VSIST1	YES	06May2019	HY	YES
002	VSIST2	YES	13Jun2019	CH	YES
003	VSIST1	YES	22Apr2019	CH	YES
004	VSIST1	YES	11Sep2019	MISSING	NO
004	VSIST2	YES	05Nov2019	MULTIPLE	YES
004	VSIST2	YES	03Dec2019	MISSING	NO
005	VSIST1	YES	12Dec2019	MULTIPLE	YES
006	VSIST1	YES	30Oct2019	MS	YES
007	VSIST1	YES	23Oct2019	CA	YES
008	VSIST1	YES	02Jan2020	BJ	YES

Data set 03 /Table 03:

SUBNUM	VISNAME	TEST_DEC	TESTDAT	LOCATION_NAME	YES/NO
004	VSIST2	YES	05Nov2019	LOC_4	YES
004	VSIST2	YES	05Nov2019	LOC_6	YES
005	VSIST1	X	12Dec2019	LOC_5	YES
005	VSIST1	X	12Dec2019	LOC_8	YES
005	VSIST1	X	12Dec2019	LOC_10	YES

Re: array or transpose for data

ed_sas_member — Mon, 03 Feb 2020 11:43:34 GMT

Hi @raja777pharma

Here is a proposal to achieve this.

Let me know if that does meet your expectations!

Best,

data dataset1;
	infile datalines dlm="09"x;
	input SUBNUM VISNAME $ TEST_DEC $ TESTDAT:date9. NY $ NJ $ CA $ CH $ DH $ HY $ MU $ LV $ BJ $ MS $ LN $;
	datalines;
001	VSIST1	X	16Apr2019	 	 	X	 	 	 	 	 	 	 	 
002	VSIST1	X	06May2019	 	 	 	 	 	X	 	 	 	 	 
002	VSIST2	X	13Jun2019	 	 	 	X	 	 	 	 	 	 	 
003	VSIST1	X	22Apr2019	 	 	 	X	 	 	 	 	 	 	 
004	VSIST1	X	11Sep2019	 	 	 	 	 	 	 	 	 	 	 
004	VSIST2	X	05Nov2019	 	 	 	X	 	X	 	 	 	 	 
004	VSIST2	X	03Dec2019	 	 	 	 	 	 	 	 	 	 	 
005	VSIST1	X	12Dec2019	 	 	 	 	X	 	 	X	 	X	 
006	VSIST1	X	30Oct2019	 	 	 	 	 	 	 	 	 	 	 
007	VSIST1	X	23Oct2019	 	 	X	 	 	 	 	 	 	 	 
008	VSIST1	X	02Jan2020	 	 	 	 	 	 	 	 	X	 	 
;
run;

/*DATASET 2 */ 

data dataset2_temp1;
	set dataset1;
	by SUBNUM VISNAME TEST_DEC TESTDAT;
	array _location (*) $ NY NJ CA CH DH HY MU LV BJ MS LN;
	do i=1 to dim(_location);
		if _location(i) ne '' then do;
			LOCATION_NAME = vname(_location(i));
			output;
		end;
	end;
	if "X" not in _location then do;
			LOCATION_NAME = 'MISSING';
			output;
		end;
	drop i NY NJ CA CH DH HY MU LV BJ MS LN;
run;

proc transpose data=dataset2_temp1 out=dataset2_temp2 (drop= _name_);
	var LOCATION_NAME;
	by SUBNUM VISNAME TEST_DEC TESTDAT;
run;

data dataset2;
	set dataset2_temp2;
	format TESTDAT date9.;
	length LOCATION_NAME $ 20;
	LOCATION_NAME = catx(" ",of col:);
	if countw(LOCATION_NAME) > 1 then LOCATION_NAME = 'MULTIPLE';
	YES_NO = "YES";
	if LOCATION_NAME = 'MISSING' then YES_NO = "NO";
	drop col:;
run;

/*DATASET 3 */ 

data dataset3_temp1;
	set dataset1;
	format TESTDAT date9.;
	array _loc (*) $ NY NJ CA CH DH HY MU LV BJ MS LN;
	do i=1 to dim(_loc);
		if _loc(i) ne '' then do;
			LOCATION_NAME = compress("LOC_"||i);
			YES_NO = "YES";
			output;
		end;
	end;
	drop NY NJ CA CH DH HY MU LV BJ MS LN;
run;

proc sql;
	create table dataset3 as
	select SUBNUM, VISNAME, TEST_DEC, TESTDAT, LOCATION_NAME, YES_NO 
	from dataset3_temp1
	group by SUBNUM, VISNAME, TEST_DEC, TESTDAT
	having count(*)> 1
	order by SUBNUM, VISNAME, TEST_DEC, TESTDAT, i;
quit;

Re: array or transpose for data

raja777pharma — Mon, 03 Feb 2020 11:53:10 GMT

Thank you

Yes it's worked perfectly

Re: array or transpose for data

ed_sas_member — Mon, 03 Feb 2020 12:13:14 GMT

Awesome!

Thank you @raja777pharma