topic Re: Create new dummy variables from multiple columns of character variables in SAS Programming

Create new dummy variables from multiple columns of character variables

stancemcgraw — Wed, 23 Sep 2020 19:31:51 GMT

Hi, I'm trying to create a dummy variable for each character variable.

data have;

StudyID comorbidity1 comorbidity2 comorbidity3 comorbidty4

1 COPD HTN DM NONE

2 HTN COPD DM MENTAL_ILLNESS

data want;

StudyID COPD HTN DM MENTAL_ILLNESS

1 1 1 1 0

2 1 1 1 1

Re: Create new dummy variables from multiple columns of character variables

PaigeMiller — Wed, 23 Sep 2020 19:39:14 GMT

Do not create dummy variables yourself. SAS has already done this for you in many procedures, so if you are trying to create an analysis or statistical model involving character variables, then the CLASS statement in most modelling procedures are all that you need.

Re: Create new dummy variables from multiple columns of character variables

Reeza — Wed, 23 Sep 2020 19:37:36 GMT

How many different categories do you have? If it's a few it's easy enough to manually code them but if it needs to be dynamic that's more coding...and likely more stable if this is ever going to be run in the future.

Re: Create new dummy variables from multiple columns of character variables

Reeza — Wed, 23 Sep 2020 19:39:38 GMT

Not the way this data is structured - it needs to be restructured regardless here to work properly in a statistical model.

Re: Create new dummy variables from multiple columns of character variables

jimbarbour — Wed, 23 Sep 2020 21:40:53 GMT

I would solve the problem this way:

DATA	Have	(DROP=Comorbidity)	
		Columns	(KEEP=Comorbidity)
		;
	DROP	_:;
	LENGTH	StudyID			$8
			Comorbidity1	$32
			Comorbidity2	$32
			Comorbidity3	$32
			Comorbidity4	$32
			;
	INFILE	Datalines		MISSOVER;
	INPUT	StudyID			$
			Comorbidity1	$
			Comorbidity2	$
			Comorbidity3	$
			Comorbidity4	$
			;
	ARRAY	Comorbidities	[4]	Comorbidity1 - Comorbidity4;
	DO	_i			=	1	TO	4;
		Comorbidity	=	Comorbidities[_i];
		IF	UPCASE(Comorbidity)	^=	'NONE'	AND
			NOT	MISSING(Comorbidity)		THEN
			OUTPUT	Columns;
	END;
	OUTPUT	Have;
	DELETE;
DATALINES;
1           COPD              HTN              DM         NONE
2           HTN               COPD             DM         MENTAL_ILLNESS
3           DM
4           MENTAL_ILLNESS    NONE
5           DM                MENTAL_ILLNESS
;
RUN;

PROC	SORT	DATA=Columns	NODUPKEY;
	BY	Comorbidity;
RUN;

DATA	_NULL_;
	SET	Columns	NOBS=Nbr_of_Columns;
	IF	_N_	=	1	THEN
		CALL	SYMPUTX('Nbr_of_Columns', Nbr_of_Columns, 'G');
	CALL	SYMPUTX(CATS('Column', PUT(_N_, 3.)), Comorbidity, 'G');
RUN;

DATA	Want;
	SET	Have;
	DROP	Comorbidity1 - Comorbidity&Nbr_of_Columns	j;
	ARRAY	Comorbidities	[*]	Comorbidity1 - Comorbidity&Nbr_of_Columns;

	%MACRO	Init_Column_Values;
		%LOCAL	i;
		%DO	i	=	1	%TO	&Nbr_of_Columns;
			&&Column&i		=	0;
		%END;
	%MEND;
	%Init_Column_Values;

	DO	j	=	1	TO	&Nbr_of_Columns;
		SELECT(Comorbidities[j]);
			%MACRO	Set_Column_Values;
				%LOCAL	i;
				%DO	i	=	1	%TO	&Nbr_of_Columns;
						WHEN	("&&Column&i")
							&&Column&i		=	1;
				%END;
			%MEND;
			%Set_Column_Values
			OTHERWISE;
		END;
	END;
RUN;

The program is dynamic. The program will automatically adjust to however many unique values are in your data. The program will then dynamically determine which columns are populated and which are not setting a boolean (0 or 1).

Results from the above data:

Jim

Re: Create new dummy variables from multiple columns of character variables

jimbarbour — Wed, 23 Sep 2020 21:44:33 GMT

If we add made-up conditions BMI and GERI, as in the following:

1           COPD              HTN              DM         NONE
2           HTN               COPD             DM         MENTAL_ILLNESS
3           DM
4           MENTAL_ILLNESS    NONE
5           DM                MENTAL_ILLNESS
6           BMI               GERI

The program adjusts automatically, and the results are as follows:

Jim

Re: Create new dummy variables from multiple columns of character variables

Reeza — Wed, 23 Sep 2020 21:50:24 GMT

*what you start with;
DATA	Have	
		
		;
	
	LENGTH	StudyID			$8
			Comorbidity1	$32
			Comorbidity2	$32
			Comorbidity3	$32
			Comorbidity4	$32
			;
	INFILE	Datalines		MISSOVER;
	INPUT	StudyID			$
			Comorbidity1	$
			Comorbidity2	$
			Comorbidity3	$
			Comorbidity4	$
			;
DATALINES;
1           COPD              HTN              DM         NONE
2           HTN               COPD             DM         MENTAL_ILLNESS
3           DM
4           MENTAL_ILLNESS    NONE
5           DM                MENTAL_ILLNESS
;
RUN;


*transpose to long;
proc transpose data=have out=long;
by studyID;
var comorbidity1-Comorbidity4;
run;

*add indicator variable and delete empty rows;
data long2;
set long;
count=1;
if missing(col1) then delete;
run;

*transpose to wide again;
proc transpose data=long2 out=wide prefix=DX_;
by studyID;
id col1;
var count;
run;

*fill in missing with 0;
data want;
set wide;
array _dx(*) dx_:;
do i=1 to dim(_dx);
if missing(_dx(i)) then _dx(i) = 0;
end;

run;

Re: Create new dummy variables from multiple columns of character variables

stancemcgraw — Thu, 24 Sep 2020 16:50:44 GMT

this works until step 3, making data wide again it says, " The ID value "DX_Use_Choices_1000___9999" occurs twice in the same BY group".....

Re: Create new dummy variables from multiple columns of character variables

Reeza — Thu, 24 Sep 2020 17:49:04 GMT

That means you have duplicate either in the diagnosis or in the IDs?
Figure out what is duplicated and either de-duplicate it before this process or modify your example data to reflect your actual data ad I can take another look at it.

Re: Create new dummy variables from multiple columns of character variables

stancemcgraw — Thu, 24 Sep 2020 18:09:25 GMT

I used the LET option on the transpose statement and that seemed to work. Now I just need to remove the DX_ suffix from each variable. Not sure how to do that...

Re: Create new dummy variables from multiple columns of character variables

Reeza — Thu, 24 Sep 2020 19:21:43 GMT

prefix = option on PROC TRANSPOSE added those - I do that to make it easier to reference all the variables at once within an array. You can add IDLABEL so that your labels are clean and use those instead when printing/displaying the report.

Re: Create new dummy variables from multiple columns of character variables

jimbarbour — Thu, 24 Sep 2020 23:01:54 GMT

If you really needed the column names without the Dx_ prefix, a little macro that would strip them off can be written like so:

%MACRO	Remove_Prefix_From_Var(Lib=, Set=, Pfx=, Debug=NO);
	%LOCAL	Cmnt;
	%LOCAL	Vars;

	%IF	%QUPCASE(&Debug)	=	YES	%THEN
		%DO;
			%LET	Cmnt	=	;
			%PUT	&Nte1  Debug is on in macro &SysMacroName;
		%END;
	%ELSE
		%DO;
			%LET	Cmnt	=	*;
		%END;

	PROC	CONTENTS	DATA=&Lib..&Set
						OUT	=WORK.&Set.V
						VARNUM
						NOPRINT
						;
	RUN;

	PROC	SQL	NOPRINT;
		SELECT	CATS(Name, '=', TRANWRD(lowcase(SUBSTR(Name, 1, LENGTH("&Pfx"))), lowcase("&Pfx"), ''), SUBSTR(Name, LENGTH("&Pfx") + 1))
			AS	Work_Var	LENGTH=512
			INTO	:	Vars	SEPARATED BY ' '
			FROM	WORK.&Set.V
			;
	QUIT;

	%&Cmnt.PUT	NOTE:  &=Vars;

	PROC	DATASETS	LIBRARY=&Lib	NOLIST;
		MODIFY	&Set;
			RENAME	
				&Vars
				;
	QUIT;
%MEND	Remove_Prefix_From_Var;

I used SAShelp.gas to generate test data like so:

data	work.gas;
	set	sashelp.gas(Rename=(cpratio=dx_CpRatio
							EqRatio=dx_EqRatio
							Fuel=Dx_Fuel
							NOx=Dx_NOx))
							;
run;

**-----------------------------------------------------------------------------**;

%Remove_Prefix_From_Var(Lib=WORK, Set=Gas, Pfx=Dx_, Debug=YES);

But of course you have actual data.

If it's useful, great. Otherwise, carry on.

Jim