Can i create this macro variable using a proc sql into or call symput in a data step. I tried creating one but the first 3 variables that i don't want the sum of was also getting summed as i was using it for the entire dataset but i want to make it flexible and don't want to sum the first 3 variables but all other after that. I will paste my code at the end of this:
%let selectvars = var1, var2, var3, sum(A_Count) as Sum_of_A_Count, sum(E_Count_GP)as Sum_of_E_Count_GP,
sum(E_Count_BE19) as Sum_of_E_Count_BE19, sum(E_Count_Prc) as Sum_of_E_Count_Prc,
sum(V_Count_GP) as Sum_of_V_Count_GP, sum(V_Count_BE19) as Sum_of_V_Count_BE19,
sum(V_Count_Prc) as Sum_of_V_Count_Prc, sum(O_Count) as Sum_of_O_Count,
sum(A_ADR) as Sum_of_A_ADR, sum(E_ADR_GP) as Sum_of_E_ADR_GP,
sum(E_ADR_BE19) as Sum_of_E_ADR_BE19, sum(E_ADR_Prc) as Sum_of_E_ADR_Prc,
sum(V_ADR_GP) as Sum_of_V_ADR_GP, sum(V_ADR_BE19) as Sum_of_V_ADR_BE19,
sum(V_ADR_Prc) as Sum_of_V_ADR_Prc, sum(A_BDR) as Sum_of_A_BDR,
sum(E_BDR_GP) as Sum_of_E_BDR_GP, sum(E_BDR_BE19) as Sum_of_E_BDR_BE19,
sum(E_BDR_Prc) as Sum_of_E_BDR_Prc, sum(V_BDR_GP) as Sum_of_V_BDR_GP,
sum(V_BDR_BE19) as Sum_of_V_BDR_BE19, sum(V_BDR_Prc) as Sum_of_V_BDR_Prc,
sum(O_Amt) as Sum_of_O_Amt
;
Code i used :
proc sql;
select cat("(","A.",strip(name)," - ","B.",strip(name),")"," as "," ",strip(name))
into : diff_var
seperated by ","
from dictionary.columns
where Libname = "WORK" and memname = upcase("ADHOC_Pivot");
quit;
Thanks,
Luckily, your first 3 variables, which you do not want summed, stand out because they do not have an underscore in them.
Just use that to create the summary expressions with SQL:
proc sql noprint;
select cats("Sum(",name,") as Sum_of_",name) into : sums separated by ","
from dictionary.columns
where Libname = "WORK" and memname = upcase("ADHOC_Pivot") and name ? '_';
quit;
%let selectvars=var1,var2,var3,&sums;
I changed the CAT function to CATS, so I did not have to use the STRIP function.
Or, if you do not want to sum the first 3 variables (but all the others), and you want the names of the first 3 variables to be generated automatically also, you could do this:
proc sql noprint;
select case
when varnum<=3 then name
else cats("Sum(",name,") as Sum_of_",name)
end into : selectvars separated by ","
from dictionary.columns
where Libname = "WORK" and memname = upcase("ADHOC_Pivot");
quit;
Could you please provide some sample data in datalines of the table ADHOC_Pivot and clarify what value you expect to in the macrovariable?
Best,
What is your group by ? If you don't group by var1, var2, var3 SQL will automatically remerge the sums.
Have you tried using Proc MEANS to compute the sums? MEANS is very likely much easier to code than what you are attempting and does not require macro.
Example:
Simulated data
* simulate some data; data have; call streaminit(123); do one = 1 to 3; do two = 1 to 10; do three = 1 to 5; do instance = 1 to 20 + rand('integer', 2,12); array x a_count e_count_gp e_count_be19 e_count_prc; do over x; x = rand('integer', 1, 20); end; output; end; end; end; end;
drop instance; run;
Proc MEANS
proc means noprint data=have;
by one two three;
output out=sums sum= / autoname;
run;
var1, var2 and var3 are my grouping variables.
Luckily, your first 3 variables, which you do not want summed, stand out because they do not have an underscore in them.
Just use that to create the summary expressions with SQL:
proc sql noprint;
select cats("Sum(",name,") as Sum_of_",name) into : sums separated by ","
from dictionary.columns
where Libname = "WORK" and memname = upcase("ADHOC_Pivot") and name ? '_';
quit;
%let selectvars=var1,var2,var3,&sums;
I changed the CAT function to CATS, so I did not have to use the STRIP function.
Or, if you do not want to sum the first 3 variables (but all the others), and you want the names of the first 3 variables to be generated automatically also, you could do this:
proc sql noprint;
select case
when varnum<=3 then name
else cats("Sum(",name,") as Sum_of_",name)
end into : selectvars separated by ","
from dictionary.columns
where Libname = "WORK" and memname = upcase("ADHOC_Pivot");
quit;
Thanks @s_lassen this worked perfectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: