How to create multiple dummy variables from observations with many inputs

[CynthiaWei]

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,

C

[PeterClemmensen]

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

So should Fiber_7 and Fiber_8 be variables too?

[CynthiaWei]

Yes!  Fiber_7 and Fiber_8 should be variables too!

Sorry for the confusion!

Thank you!

[PeterClemmensen]

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;

[CynthiaWei]

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.

[nicobuettner]

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!!! 

[CynthiaWei]

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,

C

[PeterClemmensen]

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

[CynthiaWei]

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!

[PeterClemmensen]

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;

[CynthiaWei]

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!

[CynthiaWei]

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!

[PeterClemmensen · Accepted solution]

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;

[CynthiaWei]

I really appreciate your instruction and the codes!

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

Best regards,

C

[PeterClemmensen]

Anytime, glad to help 🙂