Macro Quoting Function and total by Grouped variable

Reply
Frequent Contributor
Posts: 81

Macro Quoting Function and total by Grouped variable

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

Trusted Advisor
Posts: 1,214

Re: Macro Quoting Function and total by Grouped variable

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

Frequent Contributor
Posts: 81

Re: Macro Quoting Function and total by Grouped variable

Hi Quentin,

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

thanx

Super User
Super User
Posts: 6,364

Re: Macro Quoting Function and total by Grouped variable

What did you try?  How close did you get?

Frequent Contributor
Posts: 81

Re: Macro Quoting Function and total by Grouped variable

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Macro Quoting Function and total by Grouped variable

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

Frequent Contributor
Posts: 81

Re: Macro Quoting Function and total by Grouped variable

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

Super User
Super User
Posts: 6,364

Re: Macro Quoting Function and total by Grouped variable

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;

Frequent Contributor
Posts: 81

Re: Macro Quoting Function and total by Grouped variable

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

Ask a Question
Discussion stats
  • 8 replies
  • 271 views
  • 4 likes
  • 4 in conversation