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
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;
Should Fiber_n only go to n=5 or is that for demonstration?
So should Fiber_7 and Fiber_8 be variables too?
Yes! Fiber_7 and Fiber_8 should be variables too!
Sorry for the confusion!
Thank you!
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;
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.
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!!!
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
I dont understand that. Do your want a FIber_-3 kind of variable as well?
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!
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;
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!
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!
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;
I really appreciate your instruction and the codes!
They work very well! And I can play with them right now! Super!!!
Best regards,
C
Anytime, glad to help 🙂
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.