I have a variable that has about 50 potential values which have no prefix/suffix or any pattern in them. I need to create a variable named that value and set the new variable to 1 with the other 49 new variables in that row to be set to 0.
Here is a small sample of my current data set.
Here is what I want.
data temp / view=temp;
set have;
count=1;
run;
proc transpose data=have out=wide prefix=State_;
by area name;
id type_code;
var count;
run;
data want;
set wide;
array _state(*) state_;
do i=1 to dim(_state);
if missing(_state(i)) then _state(i) = 0;
end;
run;
Fully dynamic above.
Alternatively:
proc sql noprint;
select distinct type_code into : code_list separated by " "
from have;
quit;
data want;
set have;
array states(*) &code_list.; *list your codes manually;
do i=1 to dim(states);
if vname(states(i)) = type_cd then states(i) = 1;
else states(i) = 0;
end;
run;
Why?
Not trying to cause problems but this sort of dummy variable is often added to do certain forms of regression and as such are generally not needed as a Class variable behaves as these dummies would.
First of all: do not post pictures of data. To supply example data, use a data step with datalines which you post into a code box opened with the "little running man" button right next to the one indicated:
(the indicated button is for logs and other textual data where formatting is critical).
Your issue looks like a task for proc transpose, but in your case you only create redundant information; what are you trying to achieve in the end?
I tried, but for whatever reason, it would allow it.
I'm trying to create counters for a data set. I've tried proc transpose, but it doesn't seem to get me where I want to be. I'm trying to avoid having to write 50 if/then statements. Perhaps something with Proc Transpose and then a do loop of some sort?
data temp / view=temp;
set have;
count=1;
run;
proc transpose data=have out=wide prefix=State_;
by area name;
id type_code;
var count;
run;
data want;
set wide;
array _state(*) state_;
do i=1 to dim(_state);
if missing(_state(i)) then _state(i) = 0;
end;
run;
Fully dynamic above.
Alternatively:
proc sql noprint;
select distinct type_code into : code_list separated by " "
from have;
quit;
data want;
set have;
array states(*) &code_list.; *list your codes manually;
do i=1 to dim(states);
if vname(states(i)) = type_cd then states(i) = 1;
else states(i) = 0;
end;
run;
I think this might work. I'll give it a go and let you know.
@learningsas101 wrote:
I tried, but for whatever reason, it would allow it.
I'm trying to create counters for a data set. I've tried proc transpose, but it doesn't seem to get me where I want to be. I'm trying to avoid having to write 50 if/then statements. Perhaps something with Proc Transpose and then a do loop of some sort?
Count what exactly? Or what kind of counters?
If it is levels of Type_cd then proc freq will count that quite handily.
Proc freq won't work for what I want.
Two others have already posted solutions that will likely work. I'll test them out today or tomorrow and let everyone know.
@learningsas101 wrote:
Proc freq won't work for what I want.
Two others have already posted solutions that will likely work. I'll test them out today or tomorrow and let everyone know.
But you haven't described what you are doing.
When I see 15 variables with values of 0/1 I do not see any "counting" going on.
Please try below code
data have;
input type_cd$ area name$;
cards;
AZ 3 Lucy
BH 5 Frank
CR 1 John
IM 8 Susan
KS 10 Mary
LL 9 Jill
;
proc sql noprint;
select distinct type_cd into: valv separated by ' ' from have;
quit;
data want;
set have;
array vals(&sqlobs) &valv;
do i = 1 to &sqlobs;
if vname(vals(i))=type_cd then vals(i)=1;
else vals(i)=0;
end;
drop i;
run;
This one will likely work as well and includes setting the values to 0 if missing and 1 if not.
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.