topic Re: Creating macro variable from a table in SAS Programming

Creating macro variable from a table

adi121 — Wed, 23 May 2018 04:46:44 GMT

data Customers;
input Cust_id Month Spend;
cards;
1 JAN 100
1 FEB 200
1 MAR 300
1 APR
2 JAN 400
2 FEB 100
2 MAR 600
3 JUN 100
;run;

%macro amex(d=,v=,l=,a=);
proc sql;
select count(distinct &v) into :n from &d;
select distinct &l into :mth separated by "," from &d;
select sum(spend) into :s1 from &d;
quit;

data &a;
set &d;

%mend amex;

%amex(d=customers,v=cust_id,l=Month,a=li);

need help as im new to macros

I want :

CUSTOMER_COUNT AS COUNT OF DISTINCT CUSTOMER, MONTH AS DISTINCT MONTH SEPARATED BY COMMA.

and TOTAL SPEND AS SUM OF SPEND

not getting desired output,plz tell what is wrong

Re: Creating macro variable from a table

novinosrin — Wed, 23 May 2018 05:00:48 GMT

seems like just a syntax issue to me:

data Customers;
input Cust_id Month $ Spend;
cards;
1 JAN 100
1 FEB 200
1 MAR 300
1 APR .
2 JAN 400
2 FEB 100
2 MAR 600
3 JUN 100
;run;

 

%macro amex(d=,v=,l=,a=);
proc sql;
select count(distinct &v) into :n from &d;
select distinct &l into :mth separated by "," from &d;
select sum(spend) into :s1 from &d;
quit;

%put &mth;
%put &n;
%put &s1;
data &a;
set &d;
run;
%mend amex;

 

%amex(d=customers,v=cust_id,l=Month,a=li);

Re: Creating macro variable from a table

Kurt_Bremser — Wed, 23 May 2018 06:03:35 GMT

%macro amex(d=,v=,l=,a=);
proc sql;
select count(distinct &v) into :n from &d;
select distinct &l into :mth separated by "," from &d;
select sum(spend) into :s1 from &d;
quit;
/* you just created three macro variables which are never used and will vanish once the macro ends, as they are local to the macro */

data &a;
set &d;
/* where is the run statement for the data step? Don't be sloppy */
%mend amex;

All the macro does is making a copy of dataset &d in new dataset &a; if you add additional data step code AFTER the macro call, you might create some really nasty piece of code that is next to un-maintainable.

Re: Creating macro variable from a table

RW9 — Wed, 23 May 2018 06:58:36 GMT

"need help as im new to macros" - lesson one, only use macro where it actually adds any value - which is in very limited circumstances and should be clearly detailed in a functional design specification of the process. Exact same code (but which will actually create the three macro variables as you want:

data customers;
  input cust_id month spend;
cards;
1 JAN 100
1 FEB 200
1 MAR 300
1 APR 
2 JAN 400
2 FEB 100
2 MAR 600
3 JUN 100
;
run;

proc sql noprint;
  select count(distinct cust_id) into :n from customers;
  select distinct month into :mth separated by "," from customers;
  select sum(spend) into :s1 from customers;
quit;

Next question, what do you plan on doing with the macro variables once created. The second looks like some sort of list of data items, doing it this way will lead to errors, far less robust code, and more coding effort. I can guarentee you there are simpler methods to do what you want to do.

Re: Creating macro variable from a table

adi121 — Wed, 23 May 2018 19:40:32 GMT

%macro amex(v=);
proc sql;
select count(distinct &v),Month,sum(spend) as Total
into :n1-:n4,:m1-:m4,:t1-:t4 from Customers
quit;
%put &n1 &m1 &t1;
%put &n2 &m2 &t2;
%put &n3 &m3 &t3;
%put &n4 &m4 &t4;
%mend amex;

%amex(v=cust_id);

i have changed the code but still not correct result

output is

3 JAN 1800

3 FEB 1800

3 MAR 1800

3 APR 1800

but i want output like

1 JAN,FEB,MAR,APR 600

2 JAN,FEB,MAR 1100

3 jun 100

Plz help

Re: Creating macro variable from a table

adi121 — Thu, 24 May 2018 04:38:23 GMT

im near to my result

%macro b;
proc sql;
select count(distinct cust_id),Month,sum(Spend) as Total
into :n1,:mth separated by ",",:t1 from Customers;
quit;
%put &n1 &mth &t1;
%mend b;
%b;

output is : 3 JAN,FEB ,mar,APR,JAN,FEB,MAR,JUN 1800

im not able to get distinct month

plz help

when i use distinct Month error comes as:

Apparent symbolic reference N1 not resolved.
WARNING: Apparent symbolic reference MTH not resolved.
WARNING: Apparent symbolic reference T1 not resolved.
&n1 &mth &t1

plz help.....

Re: Creating macro variable from a table

Tom — Thu, 24 May 2018 05:35:50 GMT

It looks like you are having trouble with two things.

1) Scoping of macro variables. The SELECT ... INTO syntax will write into existing macro variables. But if you run it inside a macro and the target macro variables do not already exist they will be created as local to the macro. So they will disappear when the macro finishes executing.

2) Defining what you want to select/count with your SQL.

From your other self-reply you state that you want values like

but i want output like

1 JAN,FEB,MAR,APR 600

2 JAN,FEB,MAR 1100

3 jun 100

If you want that output then make a TABLE and skip the attempt to generate macro variables.

data want ;
  set customers;
  by cust_id month ;
  length months $200 ;
  if first.cust_id then TOTAL=0;
  total + spend ;
  if first.cust_id then months=' ';
  if first.month then months=catx(',',months,month);
  if last.cust_id;
run;

How are you planning to use the macro variables? I suspect that for most applications the dataset would be of more value.