BookmarkSubscribeRSS Feed
adi121
Fluorite | Level 6

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

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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);
Kurt_Bremser
Super User
%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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

adi121
Fluorite | Level 6

%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

 

adi121
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

  

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6658 views
  • 0 likes
  • 5 in conversation