Creating a macro using call symput or proc sql into

[hiteshchauhan1]

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,

[ed_sas_member]

Hi @hiteshchauhan1 

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,

[hiteshchauhan1]

Spoiler

All the columns of the dataset ADHOC_pivot is my value for macro variable but in the form which i described in Macro Variable sumvars.

[RichardDeVen]

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

Spoiler

* 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;

[hiteshchauhan1]

var1, var2 and var3 are my grouping variables.

[s_lassen · Accepted solution]

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;

[hiteshchauhan1]

Thanks @s_lassen this worked perfectly.