topic Re: proc sql into with a where clause in SAS Programming

proc sql into with a where clause

ss59 — Thu, 25 Jan 2018 11:15:48 GMT

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;

Re: proc sql into with a where clause

ss59 — Thu, 25 Jan 2018 11:17:26 GMT

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;

Re: proc sql into with a where clause

Rhys — Thu, 25 Jan 2018 11:17:39 GMT

put the where after the from

Re: proc sql into with a where clause

RW9 — Thu, 25 Jan 2018 11:42:53 GMT

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.

Re: proc sql into with a where clause

gamotte — Thu, 25 Jan 2018 13:27:00 GMT

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;