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;
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;
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;
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.
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;
put the where after the from
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.