topic Re: How to create multiple dummy variables from observations with many inputs in SAS Data Management

How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 09:12:29 GMT

Hi SAS Pros,

I have a data set:

ID Fiber What_Fiber

1 1 1;2;3;4

2 1 1

3 1 3

4 0 999

5 0 999

6 1 1;2;3;4;8

7 1 1;3;5;7

What I want:

ID Fiber Fiber_1 Fiber_2 Fiber_3 Fiber_4 Fiber_5 Fiber_missing

1 1 1 1 1 1 0 0

2 1 1 0 0 0 0 0

3 1 0 0 1 0 0 0

4 0 0 0 0 0 0 1

5 0 0 0 0 0 0 1

6 1 1 1 1 1 1 0

7 1 1 0 1 0 1 0

Thank you so much for any help!

Kind regards,

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 09:22:35 GMT

Should Fiber_n only go to n=5 or is that for demonstration?

So should Fiber_7 and Fiber_8 be variables too?

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 09:33:48 GMT

Yes! Fiber_7 and Fiber_8 should be variables too!

Sorry for the confusion!

Thank you!

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 09:46:06 GMT

No problem. This should do it

data have;
input ID Fiber What_Fiber $ 5-13;
datalines4;
1 1 1;2;3;4  
2 1 1        
3 1 3        
4 0 999      
5 0 999      
6 1 1;2;3;4;8
7 1 1;3;5;7  
;;;;

data want(drop=i);
    set have;
    array Fiber_ {999};
    call stdize('replace', 'mult=', 0, of Fiber_[*], _N_);
    do i = 1 to countw(What_Fiber, ';');
        Fiber_ [input(scan(What_Fiber, i, ';'), 8.)] = 1;
    end;
run;

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 09:52:59 GMT

Hi,

Thank you so much for the code!

What is I use -3 to represent 999 for any missing data. It seems to not work.

Re: How to create multiple dummy variables from observations with many inputs

nicobuettner — Fri, 28 Feb 2020 09:57:41 GMT

Like this?

data temp;
  attrib ID length=8;
  attrib Fiber length=8;
  attrib What_Fiber length=$30;

  infile datalines dlm=',' dsd missover;

  input ID Fiber What_Fiber $;

datalines4;
1,1,1;2;3;4
2,1,1
3,1,3
4,0,999
5,0,999
6,1,1;2;3;4;8
7,1,1;3;5;7
;;;;
run;

%macro tmp;

%let colcnt = 8;

data _null_;
  do i = 1 to &colcnt.;
    call symput("colname" !! strip(put(i, 10.)), "Fiber_" !! strip(put(i, 10.)));
  end;
  call symput("count", strip(put(i - 1, 10.)));
run;

%do i = 1 %to &count.;
  %put &&colname&i..;
%end;

data temp2(drop=FiberMax i n);
  set temp;

  %do i = 1 %to &count.;
    attrib &&colname&i.. length=8;
    &&colname&i.. = 0;
  %end;

  array FiberArr &colname1. - &&colname&count..;

  attrib Fiber_missing length=8;

  Fiber_Missing = not(Fiber);

  FiberMax = count(What_Fiber, ';') + 1;

  do i = 1 to FiberMax;
    n = scan(What_Fiber, i, ';');
    if n ne 999 then FiberArr[n] = 1;
  end;

run;

%mend;

%tmp;

Edit: Holy cow, @PeterClemmensen was quick!!!

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 09:56:49 GMT

I dont understand that. Do your want a FIber_-3 kind of variable as well?

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 10:02:03 GMT

Yes! Sorry, I mean what if instead of what is*. It was a typo.

In my actual data set, if Fiber=0 then what_fiber=-3. So, I want to create fiber_neg3=1 to reflect this situation.

Also, I found I need to create fiber_neg1=1 for someones even fiber=1. How should I create these variables.

Thank you!

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 10:07:45 GMT

I think I understand what you man.

So if you have -3 values in your data, you can account for that with different array indexes like this. Hope it makes sense, otherwise ask 🙂

data have;
input ID Fiber What_Fiber $ 5-13;
datalines4;
1 1 1;2;3;4  
2 1 1        
3 1 3        
4 0 -3       
5 0 -3       
6 1 1;2;3;4;8
7 1 1;3;5;7  
;;;;

data want(drop=i);
    set have;
    array Fiber_ {-3 : 999} Fiber_neg3-Fiber_neg0 Fiber1-Fiber999;
    call stdize('replace', 'mult=', 0, of Fiber_[*], _N_);
    do i = 1 to countw(What_Fiber, ';');
        Fiber_ [input(scan(What_Fiber, i, ';'), 8.)] = 1;
    end;
run;

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 10:09:15 GMT

I just updated my data set:

I have a data set:

ID Fiber What_Fiber

1 1 1;2;3;4

2 1 1

3 1 3

4 0 -3

5 0 -3

6 1 1;2;3;4;8

7 1 1;3;5;7

8 1 -1

What I want:

ID Fiber Fiber_1 Fiber_2 Fiber_3 Fiber_4 Fiber_5 Fiber_6 Fiber_7 Fiber_8 Fiber_neg3 fiber_neg1

1 1 1 1 1 1 0 0 0 0 0 0

2 1 1 0 0 0 0 0 0 0 0 0

3 1 0 0 1 0 0 0 0 0 0 0

4 0 0 0 0 0 0 0 0 0 1 0

5 0 0 0 0 0 0 0 0 0 1 0

6 1 1 1 1 1 0 0 0 1 0 0

7 1 1 0 1 0 1 0 1 0 0 0

8 1 0 0 0 0 0 0 0 0 0 1

Thank you!

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 10:22:21 GMT

Thank you so much!

I used to use the following code, but it doesn't work correctly. Could you please help me to point out where did I do wrong?

fiber_1=0;
fiber_2=0;
fiber_3=0;
fiber_4=0;
fiber_5=0;
fiber_6=0;
fiber_7=0;
fiber_8=0;
fiber_20=0;

fiber_neg3=0;
fiber_neg1=0;
if find(what_fiber,'1') then fiber_1=1;
if find(what_fiber,'2') then fiber_2=1;
if find(what_fiber,'3') then fiber_3=1;
if find(what_fiber,'4') then fiber_4=1;
if find(what_fiber,'5') then fiber_5=1;
if find(what_fiber,'6') then fiber_6=1;
if find(what_fiber,'7') then fiber_7=1;
if find(what_fiber,'8') then fiber_8=1;
if find(what_fiber,'20') then fiber_20=1;

if find(what_fiber,'-3') then fiber_neg3=1;
if find(what_fiber,'-1') then fiber_neg1=1;

Thank you!

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 10:22:32 GMT

Working on my previous code, this should do it..

data have;
input ID Fiber What_Fiber $ 5-13;
datalines4;
1 1 1;2;3;4  
2 1 1        
3 1 3        
4 0 -3       
5 0 -3       
6 1 1;2;3;4;8
7 1 1;3;5;7  
8 1 -1       
;;;;

data want(drop=i What_Fiber Fiber_neg2 Fiber_neg0);
    set have;
    array Fiber_ {-3 : 8} Fiber_neg3-fiber_neg0 Fiber_1-Fiber_8;
    call stdize('replace', 'mult=', 0, of Fiber_[*], _N_);
    do i = 1 to countw(What_Fiber, ';');
        Fiber_ [input(scan(What_Fiber, i, ';'), 8.)] = 1;
    end;
run;

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 10:33:12 GMT

I really appreciate your instruction and the codes!

They work very well! And I can play with them right now! Super!!!

Best regards,

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 10:36:30 GMT

Wow, I run the code and it worked very well! I just have difficulty to understand it. Great learning lesson on SAS. This is a fancy code!

I really appreciate it a lot!

Best regards,

Re: How to create multiple dummy variables from observations with many inputs

PeterClemmensen — Fri, 28 Feb 2020 12:33:20 GMT

Anytime, glad to help 🙂

Re: How to create multiple dummy variables from observations with many inputs

CynthiaWei — Fri, 28 Feb 2020 18:28:48 GMT

Hi there, could you help me have a look at the code that I was using? The if find then fiber_1=1 code? I am having difficulty to debug them.

Thank you so much!