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
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);
%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.
"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.
%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
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.....
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.