DATA Step, Macro, Functions and more

proc sql into with a where clause

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

proc sql into with a where clause

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;

Accepted Solutions
Solution
‎01-25-2018 06:17 AM
Contributor
Posts: 42

Re: proc sql into with a where clause

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;

View solution in original post


All Replies
Solution
‎01-25-2018 06:17 AM
Contributor
Posts: 42

Re: proc sql into with a where clause

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;
Super User
Super User
Posts: 9,813

Re: proc sql into with a where clause

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.

Super Contributor
Posts: 354

Re: proc sql into with a where clause

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;
Contributor
Posts: 22

Re: proc sql into with a where clause

put the where after the from

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 137 views
  • 1 like
  • 4 in conversation