proc sql into with a where clause

[ss59]

Hi all,

I'm trying to select variable names and want to sum them to create a new variable. Following code works:

proc sql noprint;
  select distinct FieldName into : name_list separated by ',' from master_data;
  quit;

%put &name_list;

data results_final;
set results_final;
summary = sum(&name_list);
run;

Now I want to do the same thing with an additional condition that involves a where clause. This is not working. could you please help?

proc sql noprint;
  select distinct FieldName where male = 1 into : male_name_list separated by ',' from master_data;
quit;

%put &male_name_list;

data results_final;
set results_final;
summary_male= sum(&male_name_list);
run;

[ss59 · Accepted solution]

Figured it out. where clause should go at the end.

proc sql noprint;
  select distinct FieldName into : male_name_list separated by ',' from master_data where male = 1;
quit;

%put &male_name_list;

data results_final;
set results_final;
summary_male= sum(&male_name_list);
run;

[RW9]

That's a very odd way to be coding, does your data have columns for each of the names?  You would find it far simpler to normalise that data, then just sum based on logic in one simpler datastep, you can drop all the macro and proc sql then.

[gamotte]

Hello,

Here is a solution that avoids using a list of names in a macrovariable

data persons;
input name $ male;
cards;
John 1
Mary 0
Peter 1
Robert 1
Jane 0
Lucy 0
William 1
Richard 1
Anna 0
;
run;

data results;
input John Mary Michael Peter Sylvia Robert Jane Lucy William Charlotte Richard Annna;
cards;
3 4 2 3 5 4 2 5 3 4 1 4
;
run;


proc sql noprint;
    SELECT count(*) INTO :nmales
    FROM persons
    WHERE male=1;
quit;

data want;
    set results;
    array a_names(&nmales.) $ _TEMPORARY_;

    if _N_=1 then do i=1 by 1 until (eof);
        set persons (where=(male=1)) end=eof;
        a_names(i)=name;
    end;

    do i=1 to dim(a_names);
        total+vvaluex(a_names(i));
    end;

    drop i name male;
run;

[Rhys]

put the where after the from