topic Obtaining Top Observations with SYMPUTX in SAS Procedures

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 02:27:41 GMT

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

Re: Obtaining Top Observations with SYMPUTX

Haikuo — Tue, 20 Mar 2012 02:35:44 GMT

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;

Re: Obtaining Top Observations with SYMPUTX

Linlin — Tue, 20 Mar 2012 02:38:09 GMT

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

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:07:13 GMT

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

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:18:22 GMT

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

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:24:20 GMT

P.S.Also, I do not need to concatenate TOP to any of my Customer_IDs.

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:24:32 GMT

P.S.Also, I do not need to concatenate TOP to any of my Customer_IDs.

Obtaining Top Observations with SYMPUTX

Haikuo — Tue, 20 Mar 2012 16:35:25 GMT

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.

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:53:04 GMT

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

Obtaining Top Observations with SYMPUTX

InfoAlisaA — Tue, 20 Mar 2012 16:54:02 GMT

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

Obtaining Top Observations with SYMPUTX

Linlin — Tue, 20 Mar 2012 17:13:36 GMT

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;

Obtaining Top Observations with SYMPUTX

Tom — Tue, 20 Mar 2012 17:23:59 GMT

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.

Obtaining Top Observations with SYMPUTX

Linlin — Tue, 20 Mar 2012 17:31:22 GMT

Thank you Tom! - Linlin