Obtaining Top Observations with SYMPUTX

[InfoAlisaA]

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

[Haikuo]

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;

[InfoAlisaA]

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

[InfoAlisaA]

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

[Haikuo]

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.

[InfoAlisaA]

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

[InfoAlisaA]

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

[Linlin · Accepted solution]

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

[InfoAlisaA]

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

[InfoAlisaA]

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

[Linlin]

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;

[Tom]

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.

[Linlin]

Thank you Tom!  - Linlin