@Ksharp thanks! Could you give maybe an example using proc sql? ... View more

Not sure about your last comment but this code:   proc sql; select count(distinct ID) from have where group=1 and not missing(var) ; quit; gets exactly what I need:  3 observations from this data set   group id var 1 1 10 1 2 12 1 3 13 1 4 .    and 2 observations from this data set   group id var 1 1 10 1 4 12 1 4 13 1 4 .  ... View more

@Tom sorry I should have been more clear. I have three variables: group, id, and var. So I would like to count the number of distinct ids with non-missing var for group 1. Here are two examples:    Here group 1 would 3 observations that have a non-missing ids.   group id var 1 1 10 1 2 12 1 3 13 1 4 .    Here group 1 would have only one non missing observations:   group id var 1 1 10 1 1 12 1 1 13 1 1 .    ... View more

@Tom @Patrick I would like to count the number of distinct non-missing observations for the first group. So let's say you have three groups, group would equal 1, 2, or 3. So then I would like to count only when group=1.   ... View more

@Tom thanks for all the explanations! ... View more

Great, thanks! I think I'm understanding it now. I will use the following code that you proposed and add %let for the codelists:   data have; row+1; input (var_1-var_4) ($); cards; A1 C1 . . D1 C2 B3 . . . . . ; %let codesetA=('A1' 'A2' 'A3') %let codesetB=('B1' 'B2' 'B3') data want; set have; array var_[4]; do i=1 to dim(var_) until(testA); testA = var_[i] in &codesetA. ; end; do i=1 to dim(var_) until(testB); testB = var_[i] in &codesetB.; end; drop i; run; The code set though could have either "" or '', right? In other words   %let codesetA=('A1' 'A2' 'A3') would be the same as %let codesetA=("A1" "A2" "A3") ... View more

data have; row+1; input (var_1-var_4) ($); cards; A1 C1 . . D1 C2 B3 . . . . . ; data want; set have; array var_[4]; do i=1 to dim(var_) until(testA); testA = var_[i] in ('A1' 'A2' 'A3'); end; do i=1 to dim(var_) until(testB); testB = var_[i] in ('B1' 'B2' 'B3'); end; drop i; run; I will just use this code as you suggested. One question though. Why do you need until(testA)? ... View more

But this should be okay then?   %let testA=("A1" "A2" "A3"); %let testB=("B1" "B2" "B3"); data NEED; set HAVE; array cond_testA {20} VAR_1-VAR_20; do i=1 to dim (cond_testA); do j=1 to countw('&testA.', ','); if (cond_testA {i} in &testA.) then do; testA=1; end; end; end; array cond_testB {20} VAR_1-VAR_20; do i=1 to dim (cond_testB); do j=1 to countw('&testB.', ','); if (cond_testB {i} in &testB.) then do; testB=1; end; end; end; run; The problem only arises with &&&var..? Sorry I'm still confused. I'm coming from stata where it's super easy to loop through strings.   ... View more

If I use the last suggestion I would have to spell out 20 times or so the different indicators that I want to create. That's why I wanted to use the var_list macro.   Can you please explain why do j=1 to countw('&&&var..', ','); will only iterate until the first string? In the example that you provided B3 is still found if use the code above.   ... View more

@Tom I have 20 variables that contain different strings. I need to create a lot of different indicator variables depending on combinations of strings in those 20 variables. For instance if any of the 20 variables contain the strings A1, A2, and A3 the indicator testA should equal to 1 and 0 otherwise. If any of the 20 variables contain the strings B1, B2, and B3 the indicator testB should equal to 1 and 0 otherwise.   ... View more

@PaigeMiller oh I did use the advice of you, @Kurt_Bremser, and @Astounding gave me. I removed the macro and wrote the code for two cases without the macro. This is how I realized what I was missing. ... View more

I figured it out. Below it the code that works.    %let testA=("A1" "A2" "A3"); %let testB=("B1" "B2" "B3"); data NEED; set HAVE; %macro var_list; %let var_list = TESTA TESTB; %local i var; %do i=1 %to %sysfunc(countw(&var_list.)); %let var = %scan(&var_list., &i.); array cond_&var. {20} VAR_1-VAR_20; do i=1 to dim (cond_&var.); do j=1 to countw('&&&var..', ','); if (cond_&var.{i} in &&&var..) then do; &var.=1; end; end; end; %end; %mend; %var_list; run; The code works even if you start the %macro inside the data step. So I'm not sure about your point @PaigeMiller. Could you please provide an example when it matters when you start and end the macro and maybe also the code. I'm still confused where you would put  %macro var_list; %end; %mend; %var_list;   ... View more