topic Re: Need to get the result value horizontally based on the visit variable in SAS Programming

Need to get the result value horizontally based on the visit variable

ambadi007 — Sat, 10 Oct 2020 15:10:43 GMT

Dear Experts,

I need a help based on the data i provided. I need to create a new variable (in data i named as Wanted_Var) based on the visits . Need to to concatenate the result variable with a comma horizontally based on the visits for a particular id ( need the complete result to concatenate for a particular id for all the visits.) , The Wanted Variable should have added a numeric value also ( ie if its None then it should be like 0-None, or Severe it should like 1-Severe).

Below the data is available

id Visit result Wanted_Var
100 vs1 None 0-None,1-Severe,2-Moderate,2Moderate
100 vs2 Severe 0-None,1-Severe,2-Moderate,2Moderate
100 vs3 Moderate 0-None,1-Severe,2-Moderate,2Moderate
100 vs4 Moderate 0-None,1-Severe,2-Moderate,2Moderate
101 vs1 None 0-None,1-Severe
101 vs2 Severe 0-None,1-Severe
102 vs1 Moderate 2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102 vs2 Moderate 2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102 vs3 Moderate 2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102 vs4 Moderate 2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102 vs5 Severe 2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe

Re: Need to get the result value horizontally based on the visit variable

jimbarbour — Sat, 10 Oct 2020 16:48:51 GMT

I think you could do this reasonably easily if you did it in a multi step process along the lines of.

Create a data step to read in the data. I might call the data Visit_Detail.
Create an SQL step that does a COUNT(*) with a Group By ID into a macro variable called Max_Visits. Max_Visits will contain the count of the maximum number of visits for any one ID.
Create another data step that creates a SAS dataset called Visit_Summary. This data step should have a array with numeric variables Severity1 - Severity&Max_Visits. A RETAIN statement needs to be coded for Severity1 - Severity&Max_Visits. As you read, populate the first empty position in the array with the level of severity for the visit (0, 1, or 2).
In the Data step in #3, above, when the ID changes from one person to the next, write out a record containing the ID and Severity1 - Severity&Max_Visits and then clear the array. CALL MISSING(of Severity1 - Severity&Max_Visits) is a great way to clear an array.
Create a final Data step that merges Visit_Detail and Visit_Summary BY ID. The information in Visit_Summary should be formatted and placed into a variable, Wanted_Var, based on the contents of Severity1 - Severity&Max_Visits. Variables Severity1 - Severity&Max_Visits should be dropped.

That in a nutshell should give you what you want. Give it a try. If you get stuck, post here. I'm headed out (it's Saturday here), but I'll be back in a few hours, and I we can talk through things if you get stuck.

Good luck!

Jim

Re: Need to get the result value horizontally based on the visit variable

PGStats — Sat, 10 Oct 2020 19:54:33 GMT

Do this in one step:

data have;
input id  Visit $  result $;
datalines;
100 vs1 None
100 vs2 Severe
100 vs3 Moderate
100 vs4 Moderate
101 vs1 None    
101 vs2 Severe
102 vs1 Moderate
102 vs2 Moderate
102 vs3 Moderate
102 vs4 Moderate
102 vs5 Severe
;

data want;
do until(last.id);
    set have; by id;
    length list $100;
    list = catx(",", list, catx("-", whichc(result,"None", "Severe", "Moderate")-1, result));
    end;
do until(last.id);
    set have; by id;
    output;
    end;
run;

proc print data=want noobs; run;

Re: Need to get the result value horizontally based on the visit variable

ambadi007 — Sun, 11 Oct 2020 03:20:03 GMT

could you please provide me a code for this, then it would be great

Re: Need to get the result value horizontally based on the visit variable

jimbarbour — Sun, 11 Oct 2020 14:37:22 GMT

I would encourage you to give it a try rather than just copying code; however, that is up to you. Results and code are below.

Jim

First, results:

Then, code:

DATA	Visit_Detail;
	INFILE	DATALINES	MISSOVER;
	INPUT	ID	
			Visit		$	
			Severity	:	$32.;
DATALINES;
100 vs1 None
100 vs2 Severe
100 vs3 Moderate
100 vs4 Moderate
101 vs1 None    
101 vs2 Severe
102 vs1 Moderate
102 vs2 Moderate
102 vs3 Moderate
102 vs4 Moderate
102 vs5 Severe
;
RUN;

PROC	SQL;
	SELECT	MAX(Visits)	INTO		:	Max_Visits
		FROM	(SELECT	COUNT(1)	AS	Visits
				FROM	Visit_Detail
				GROUP	BY	ID)
				;
QUIT;

%LET	Max_Visits	=	%SYSFUNC(STRIP(&Max_Visits));
%PUT	NOTE:  &=Max_Visits;

DATA	Visit_Summary;
	DROP	Visit	Severity _:;
	SET	Visit_Detail;
		BY	ID;
	ARRAY	_Severity	[*]	$32	_Severity1 - _Severity&Max_Visits;
	RETAIN						_Severity1 - _Severity&Max_Visits;

	SELECT	(PROPCASE(Severity));
		WHEN	("Moderate")
			Severity			=	"2-Moderate";
		WHEN	("Severe")
			Severity			=	"1-Severe";
		WHEN	("None")
			Severity			=	"0-None";
	END;

	DO	_i	=	1	TO	&Max_Visits;
		IF	MISSING(_Severity[_i])	THEN
			DO;
				_Severity[_i]	=	Severity;
				_i				=	&Max_Visits;
			END;
	END;

	IF	LAST.ID	THEN
		DO;
			Severity_Coded		=	CATX(',', OF _Severity1 - _Severity&Max_Visits);
			OUTPUT;
			CALL	MISSING	(of	_Severity1 - _Severity&Max_Visits);
		END;
RUN;

PROC	SQL;
	CREATE	TABLE	Final_Visits		AS
	SELECT	D.*	
			,S.*
		FROM	Visit_Detail			D
		INNER	JOIN	Visit_Summary	S
			ON	D.ID	=	S.ID
			;
QUIT;

id	Visit	result	Wanted_Var
100	vs1	None	0-None,1-Severe,2-Moderate,2Moderate
100	vs2	Severe	0-None,1-Severe,2-Moderate,2Moderate
100	vs3	Moderate	0-None,1-Severe,2-Moderate,2Moderate
100	vs4	Moderate	0-None,1-Severe,2-Moderate,2Moderate
101	vs1	None	0-None,1-Severe
101	vs2	Severe	0-None,1-Severe
102	vs1	Moderate	2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102	vs2	Moderate	2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102	vs3	Moderate	2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102	vs4	Moderate	2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe
102	vs5	Severe	2-Moderate,2-Moderate,2-Moderate,2-Moderate,1-Severe