Hello Everyone,
I am currently working with the following program for my homework assignment:
proc means data=orion.order_fact nway noprint;
var Total_Retail_Price;
class Customer_ID;
output out=customer_sum sum=CustTotalPurchase;
run;
proc sort data=customer_sum ;
by descending CustTotalPurchase;
run;
proc print data=customer_sum(drop=_type_);
run;
What the assignment is calling for is that I need to create a macro called TOP3 that contains the customer IDs of the top three customers after the sort of customer_sum.
This is what I created:
data _null_;
set customer_sum (firstobs=1 obs=3);
call symputx('TOP3',Customer_ID);
run;
And this is my proc print statement:
proc print data=orion.customer_dim;
var Customer_ID Customer_Name Customer_Type;
where Customer_ID=&TOP3;
run;
When I try to run my proc print statement, I am only getting the last out of the top three observations from my macro.
I am not sure if set up my macro incorrectly or if I am doing my proc print statement incorrectly.
Could someone please take a look at my code and let me know where I am going wrong with my program?
Thank you!
Alisa
data have;
input id c_id $;
cards;
30 a
20 b
10 c
5 d
3 e
;
/* if your ID is a character variable */
data _null_;
set have (obs=3) end=last;
length top $ 60;
retain top;
top=catx(',',top,quote(trim(c_id)));
if last then
call symputx('top3',top);
run;
%put &top3;
proc print data=have;
where c_id in (&top3);
run;
/* if ID is a num. variable */
proc sql noprint;
select id into:top3 separated by ','
from have(obs=3);
quit;
%put &top3;
proc print data=have;
where id in (&top3);
run;
Linlin
In your code, macro variable has been reassigned 3 times, in the end, it took the last value. You probaly will need;
data _null_;
length top3 $50;
do _n_=1 to 3;
set customer_sum ;
top3=catx(', ', top3, Customer_ID);
end;
call symputx('TOP3',top3);
run;
or:
proc sql;
select Customer_ID into :top3 separated by ' ,'
from customer_sum(obs=3);
quit;
Regards,
Haikuo
Edit : to make your downstream code working:
proc print data=orion.customer_dim;
var Customer_ID Customer_Name Customer_Type;
where Customer_ID in (&TOP3);
run;
Hi Hai.kuo,
I tried your code into my code, and unfortunately, with your data _null_ statement, I got all 75 records in the file and not just the first three. Unfortunately, I cannot use the SQL statement you provided since the assignment is calling for a symputx statement.
Also, I tried the proc print statement you provided, and it just provided only one observation.
If you have any other suggestions though, I would be open to them.
Thanks!
Alisa
P.S.Also, I do not need to concatenate TOP to any of my Customer_IDs.
Alisa,
That is odd. I have tried on Linlin's sample, and my code seems to work:
226 data _null_;
227 length top3 $50;
228 do _n_=1 to 3;
229 set have ;
230 top3=catx(', ', top3, c_ID);
231 end;
232 call symputx('TOP3',top3);
233 run;
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
SYMBOLGEN: Macro variable TOP3 resolves to a, b, c
234
235 %put &top3;
a, b, c
Are you sure you have applied _n_=1 to 3 loop?
Or maybe let's get rid of the explixit loop and use 'retain':
data _null_;
length top3 $50;
retain top3;
set customer_sum (obs=3) ;
top3=catx(', ', top3, Customer_ID);
call symputx('TOP3',top3);
run;
I hope it works for you.
Hi Hai.kuo,
I took a look at my code again and revised it so it works now.
Thanks for pointing this out to me.
Alisa
Hi Linlin,
Here is my final code that produced the right answer:
proc means data=orion.order_fact nway noprint;
var Total_Retail_Price;
class Customer_ID;
output out=customer_sum sum=CustTotalPurchase;
run;
proc sort data=customer_sum ;
by descending CustTotalPurchase;
run;
data _null_;
set customer_sum (obs=3) end=last;
length top $16.;
retain top;
top=catx(',',top,trim(Customer_ID));
if last then
call symputx('toptest',top);
run;
proc print data=orion.customer_dim;
var Customer_ID Customer_Name Customer_Type;
where Customer_Id in (&toptest);
run;
Thanks so much for your help!!
Alisa
data have;
input id c_id $;
cards;
30 a
20 b
10 c
5 d
3 e
;
/* if your ID is a character variable */
data _null_;
set have (obs=3) end=last;
length top $ 60;
retain top;
top=catx(',',top,quote(trim(c_id)));
if last then
call symputx('top3',top);
run;
%put &top3;
proc print data=have;
where c_id in (&top3);
run;
/* if ID is a num. variable */
proc sql noprint;
select id into:top3 separated by ','
from have(obs=3);
quit;
%put &top3;
proc print data=have;
where id in (&top3);
run;
Linlin
Hello Linlin,
My Customer_ID is a numeric value, but I cannot use a SQL statement with this assignment. I have to use a SYMPUTX statement.
I understand that the SYMPUTX statement changes numeric to character.
Do you think it is possible to put a put statement once the macro is set up in order to convert it back to a numeric variable?
Thanks,
Alisa
P.S.Also, I do not need to concatenate TOP to any of my Customer_IDs.
Hi Alisa,
Glad you solved your problem! to convert character to numeric, you need to use input:
%let a=20;
data _null_;
b=input("&a",2.);
put b;
run;
Not when the characters are already in a macro variable. Remember that code generated by macros or macro variable expansion is treated by SAS just as if it had been typed into the original program.
%let a=20;
data _null_;
b=&a ;
c=20;
run;
Both B and C will be numeric variables with the value of 20.
Thank you Tom! - Linlin
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.