topic Re: create multiple dummy variables in SAS Programming

create multiple dummy variables

sasprogramming — Tue, 11 May 2021 07:13:06 GMT

I want to create a dummy variable column for every code. This normally wouldn't be hard but because I am looking for substrings in a string it has stumped me.

Current data set:

ID	Code
1280	A001B007
2125	A001B007
2170	B002
2413	A001C023
2440	C023

Desired data set:

ID	A001	B002	B007	C023
1280	1	0	1	0
2125	1	0	1	0
2170	0	1	0	0
2413	1	0	0	1
2440	0	0	0	1

Thanks for any help.

Re: create multiple dummy variables

Kurt_Bremser — Tue, 11 May 2021 07:37:38 GMT

data long;
set have;
length name $4;
value = 1;
do i = 1 to length(code) by 4;
  name = substr(code,i,4);
  output;
end;
keep id name value;
run;

proc transpose 
  data=long
  out=want
;
by id;
var value;
id name;
run;

/* alternatively */
proc report data=long;
column id name;
define id / group;
define name / "" across;
run;

Untested, posted from my tablet.

Re: create multiple dummy variables

sasprogramming — Tue, 11 May 2021 11:45:07 GMT

This doesn't work unofortunately

Re: create multiple dummy variables

Kurt_Bremser — Tue, 11 May 2021 13:00:16 GMT

"Doesn't work" on its own is not helpful. Please invest some effort into your answers.

If you mean that you want zeroes instead of missing values, you need a standardization step after the transpose:

proc transpose 
  data=long
  out=pre_want;
;
by id;
var value;
id name;
run;

proc stdize
  data=pre_want
  out=want (drop=_name_)
  missing=0
  reponly
;
var _numeric_;
run;

proc print data=want noobs;
run;

Result:

ID	A001	B007	B002	C023
1280	1	1	0	0
2125	1	1	0	0
2170	0	0	1	0
2413	1	0	0	1
2440	0	0	0	1

or, for the PROC REPORT, add

options missing=0;

(same result)

Re: create multiple dummy variables

sasprogramming — Wed, 12 May 2021 00:06:51 GMT

The code is working almost fine, what I end up with is the following:

ID name value
1280 A001 1
1280 B007 1
2125 A001 1
2170 B007 1
2413 A001 1
2413 C023 1
2440 C023 1

Re: create multiple dummy variables

andreas_lds — Wed, 12 May 2021 05:02:32 GMT

@sasprogramming wrote:
The code is working almost fine, what I end up with is the following:

ID name value
1280 A001 1
1280 B007 1
2125 A001 1
2170 B007 1
2413 A001 1
2413 C023 1
2440 C023 1

Seems that something is wrong in the code you actually submitted. Please share the code using "Insert SAS Code" button.

Re: create multiple dummy variables

Kurt_Bremser — Thu, 13 May 2021 11:13:31 GMT

@sasprogramming wrote:
The code is working almost fine, what I end up with is the following:

ID name value
1280 A001 1
1280 B007 1
2125 A001 1
2170 B007 1
2413 A001 1
2413 C023 1
2440 C023 1

That's the "long" dataset; from this you run TRANSPOSE and STDIZE, or PROC REPORT with OPTIONS MISSING=0.