topic Re: Adding a new variable for each value of variable in SAS Programming

Adding a new variable for each value of variable

learningsas101 — Tue, 08 Sep 2020 15:03:20 GMT

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.

Re: Adding a new variable for each value of variable

ballardw — Tue, 08 Sep 2020 15:07:04 GMT

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.

Re: Adding a new variable for each value of variable

Kurt_Bremser — Tue, 08 Sep 2020 15:11:19 GMT

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?

Re: Adding a new variable for each value of variable

learningsas101 — Tue, 08 Sep 2020 15:23:20 GMT

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?

Re: Adding a new variable for each value of variable

Reeza — Tue, 08 Sep 2020 15:40:03 GMT

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;

Re: Adding a new variable for each value of variable

Jagadishkatam — Tue, 08 Sep 2020 15:45:15 GMT

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;

Re: Adding a new variable for each value of variable

ballardw — Tue, 08 Sep 2020 15:45:40 GMT

@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.

Re: Adding a new variable for each value of variable

learningsas101 — Tue, 08 Sep 2020 15:54:47 GMT

I think this might work. I'll give it a go and let you know.

Re: Adding a new variable for each value of variable

learningsas101 — Tue, 08 Sep 2020 17:26:56 GMT

This one will likely work as well and includes setting the values to 0 if missing and 1 if not.

Re: Adding a new variable for each value of variable

learningsas101 — Tue, 08 Sep 2020 17:28:14 GMT

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.

Re: Adding a new variable for each value of variable

ballardw — Tue, 08 Sep 2020 19:17:37 GMT

@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.