Macro Quoting Function and total by Grouped variable

[AnandSahu]

Hi All,

I have to queries here..

1. I am unable to write a code which is provided by following print procedure:

proc print data = DSN;

Var Gender;

by Salary;

Sumby Salary;

run;

So please help me to get the same result using Data Step which is given by the above code.

2. Its so difficult to get understood the difference between %STR and %BQUOTE. I know that the %STR is a compile time macro macro variable and %BQUOTE is a execution time macro quoting function. so please explain these functions with a basic example so that it would help me to understand.

Thanks

Anand

[Quentin]

Hi,

I don't understand your first question.  Why can't you use a PROC PRINT step?  You want to use a data _NULL_ step with PUT statements instead to make the report?

For second question, I think this blog post has a great explanation of difference between compile time and execution time macro quoting:

http://blogs.sas.com/content/sgf/2014/08/15/macro-quoting-made-easy/

HTH,

-Ql

[AnandSahu]

Hi Quentin,

There is no problem in using proc print but I just want to achieve the same result in data step.

thanx

[Tom]

What did you try?  How close did you get?

[AnandSahu]

Hi Tom,

I was trying for the same result by using last.var with retain statement but it doesn't give the desired ouptput.

data want;

set have;

by id;

retain var marks1;

var=id;

if last.id then do;

var = var + Marks1;

output;

end;

run;

And if I use first. or last. a new variable gets created which I dont want.

[RW9]

Can you supply some test data and required output so I can see what you are trying to achieve.

[AnandSahu]

Hi Tom,

Can't we write a code in data step to achieve the result as same as get in using print procedure.

Regards

Anand

[Tom]

So your BY variable is ID and what variables do you want to print and which variables do you want to SUM?

Is this close to what you want?

data have ;

  infile cards truncover;

  id+1;

  do until (mark1=.);

    input mark1 @;

    if mark1 ne . then output;

  end;

cards;

1 2 3

4 5

;;;;

data want ;

  set have ;

  by id;

  if first.id then sum_mark1=mark1;

  else sum_mark1+mark1;

  put id= mark1=;

  if last.id then put sum_mark1=;

run;

[AnandSahu]

Please see the result which achieved by code:

USA  Road Bike  Trek 5000 $2,200

USA  Road Bike  Cannondale 2000 $2,100

USA  Mountain Bike  Trek 6000 $1,200

USA  Mountain Bike  Cannondale 4000 $2,700

USA  Hybrid  Trek 4500 $650

France  Road Bike  Trek 3400 $2,500

France  Road Bike  Cannondale 900 $3,700

France  Mountain Bike  Trek 5600 $1,300

France  Mountain Bike  Cannondale  800 $1,899

France  Hybrid  Trek 1100 $540

United Kingdom  Road Bike  Trek 2444 $2,100

United Kingdom  Road Bike  Cannondale  1200 $2,123

United Kingdom  Hybrid  Trek 800 $490

United Kingdom  Hybrid  Cannondale 500 $880

United Kingdom  Mountain Bike  Trek 1211 $1,121

Italy  Hybrid  Trek 700 $690

Italy  Road Bike  Trek 4500  $2,890

Italy  Mountain Bike  Trek 3400  $1,877

proc print data = bicycles;

var Country Model Units TotalSAles;

by Model;

sumby Model;

run;

                                           The SAS System          00:49 Monday, January 12, 2009  33

-------------------------------------------- Model=Hybrid --------------------------------------------

                         Obs    Country           Model     Units    TotalSales

                           1    USA               Hybrid     4500        $2,925

                           2    France            Hybrid     1100          $594

                           3    United Kingdom    Hybrid      800          $392

                           4    United Kingdom    Hybrid      500          $440

                           5    Italy             Hybrid      700          $483

                       -----                                -----    ----------

                       Model                                 7600        $4,834

---------------------------------------- Model=Mountain Bike -----------------------------------------

                     Obs    Country               Model        Units    TotalSales

                       6    USA               Mountain Bike     6000        $7,200

                       7    USA               Mountain Bike     4000       $10,800

                       8    France            Mountain Bike     5600        $7,280

                       9    France            Mountain Bike      800        $1,519

                      10    United Kingdom    Mountain Bike     1211        $1,358

                      11    Italy             Mountain Bike     3400        $6,382

                   -----                                       -----    ----------

                   Model                                       21011       $34,539

------------------------------------------ Model=Road Bike -------------------------------------------

                       Obs    Country             Model      Units    TotalSales

                        12    USA               Road Bike     5000       $11,000

                        13    USA               Road Bike     2000        $4,200

                        14    France            Road Bike     3400        $8,500

                        15    France            Road Bike      900        $3,330

                        16    United Kingdom    Road Bike     2444        $5,132

                        17    United Kingdom    Road Bike     1200        $2,548

                        18    Italy             Road Bike     4500       $13,005

                     -----                                   -----    ----------

                     Model                                   19444       $47,715

                                                             =====    ==========

                                                             48055       $87,088