topic Creating a macro using call symput or proc sql into in SAS Programming

Creating a macro using call symput or proc sql into

hiteshchauhan1 — Fri, 12 Jun 2020 12:04:55 GMT

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,

Re: Creating a macro using call symput or proc sql into

ed_sas_member — Fri, 12 Jun 2020 12:13:41 GMT

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,

Re: Creating a macro using call symput or proc sql into

RichardDeVen — Fri, 12 Jun 2020 12:29:57 GMT

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;

Re: Creating a macro using call symput or proc sql into

s_lassen — Fri, 12 Jun 2020 12:49:02 GMT

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;

Re: Creating a macro using call symput or proc sql into

hiteshchauhan1 — Fri, 12 Jun 2020 12:49:40 GMT

var1, var2 and var3 are my grouping variables.

Re: Creating a macro using call symput or proc sql into

hiteshchauhan1 — Fri, 12 Jun 2020 12:51:34 GMT

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.

Re: Creating a macro using call symput or proc sql into

hiteshchauhan1 — Mon, 15 Jun 2020 08:19:04 GMT

Thanks @s_lassen this worked perfectly.