Creating macro variable from a table

Reply
Occasional Contributor
Posts: 15

Creating macro variable from a table

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

PROC Star
Posts: 1,817

Re: Creating macro variable from a table

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);
Super User
Posts: 10,259

Re: Creating macro variable from a table

%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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,599

Re: Creating macro variable from a table

"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.

Occasional Contributor
Posts: 15

Re: Creating macro variable from a table

%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

JAN,FEB,MAR          1100

3 jun                             100

 

Plz help

 

Occasional Contributor
Posts: 15

Re: Creating macro variable from a table

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.....

Super User
Super User
Posts: 8,115

Re: Creating macro variable from a table

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

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.

  

Ask a Question
Discussion stats
  • 6 replies
  • 127 views
  • 0 likes
  • 5 in conversation