BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
raja777pharma
Fluorite | Level 6

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:

SUBNUMVISNAMETEST_DECTESTDATNYNJCACHDHHYMULVBJMSLN
001VSIST1X16Apr2019  X        
002VSIST1X06May2019     X     
002VSIST2X13Jun2019   X       
003VSIST1X22Apr2019   X       
004VSIST1X11Sep2019           
004VSIST2X05Nov2019   X X     
004VSIST2X03Dec2019           
005VSIST1X12Dec2019    X  X X 
006VSIST1X30Oct2019           
007VSIST1X23Oct2019  X        
008VSIST1X02Jan2020        X  

 

Data set 01/Table 02 :

 

SUBNUMVISNAMETEST_DECTESTDATLOCATION_NAMEYES/NO
001VSIST1YES16Apr2019CAYES
002VSIST1YES06May2019HYYES
002VSIST2YES13Jun2019CHYES
003VSIST1YES22Apr2019CHYES
004VSIST1YES11Sep2019MISSINGNO
004VSIST2YES05Nov2019MULTIPLEYES
004VSIST2YES03Dec2019MISSINGNO
005VSIST1YES12Dec2019MULTIPLEYES
006VSIST1YES30Oct2019MSYES
007VSIST1YES23Oct2019CAYES
008VSIST1YES02Jan2020BJYES

 

Data set 03 /Table 03:

 

SUBNUMVISNAMETEST_DECTESTDATLOCATION_NAMEYES/NO
004VSIST2YES05Nov2019LOC_4YES
004VSIST2YES05Nov2019LOC_6YES
005VSIST1X12Dec2019LOC_5YES
005VSIST1X12Dec2019LOC_8YES
005VSIST1X12Dec2019LOC_10YES

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

 

 

Capture d’écran 2020-02-03 à 12.43.04.png

 

View solution in original post

3 REPLIES 3
ed_sas_member
Meteorite | Level 14

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;

 

 

Capture d’écran 2020-02-03 à 12.43.04.png

 

raja777pharma
Fluorite | Level 6

Thank you

Yes it's worked perfectly

ed_sas_member
Meteorite | Level 14

Awesome!

Thank you @raja777pharma 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 663 views
  • 0 likes
  • 2 in conversation