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.
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.
This doesn't work unofortunately
"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)
@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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.