topic Re: Macro Quoting Function and total by Grouped variable in SAS Programming

Macro Quoting Function and total by Grouped variable

AnandSahu — Sun, 24 Aug 2014 12:35:48 GMT

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

Re: Macro Quoting Function and total by Grouped variable

Quentin — Sun, 24 Aug 2014 13:12:35 GMT

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

Re: Macro Quoting Function and total by Grouped variable

AnandSahu — Sun, 24 Aug 2014 13:44:40 GMT

Hi Quentin,

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

thanx

Re: Macro Quoting Function and total by Grouped variable

Tom — Sun, 24 Aug 2014 15:12:54 GMT

What did you try? How close did you get?

Re: Macro Quoting Function and total by Grouped variable

AnandSahu — Thu, 28 Aug 2014 08:01:52 GMT

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.

Re: Macro Quoting Function and total by Grouped variable

RW9 — Thu, 28 Aug 2014 08:46:13 GMT

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

Re: Macro Quoting Function and total by Grouped variable

AnandSahu — Thu, 11 Sep 2014 20:06:35 GMT

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

Re: Macro Quoting Function and total by Grouped variable

Tom — Thu, 11 Sep 2014 20:17:40 GMT

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;

Re: Macro Quoting Function and total by Grouped variable

AnandSahu — Thu, 11 Sep 2014 20:42:31 GMT

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