## SAS macro programme

Hello to everyone, please would you help me in the following.

1. Create a macro program to calculate N, Mean, Std, Median, Min and Max for SALARY and for NHITS(SASHELP.BASEBALL)
2. Create output dataset using the view which is shown below (Note that “1987 Salary” and “Hits in 1986” are not variable names).

 STATISTIC 1987 Salary Hits in 1986 N XX XX Mean (Std) XX.X (XX.XX) XX.X (XX.XX) Median XX.X XX.X Min, Max XX, XX XX, XX

Keep decimal places as shown below.

 N No decimal places Mean 1 decimal place Std 2 decimal places Median 1 decimal place Min The same as it is Max The same as it is

1 ACCEPTED SOLUTION

Accepted Solutions  Ksharp
Super User

## Re: SAS macro programme

``````/*
Here is an example.
Good Luck.
*/
data have ;
set SASHELP.BASEBALL;
keep nHits Salary;
run;
proc sql;
create  view want as
select 'N' as statistic length=20,
(select put(count(Salary),best. -l) from have) as salary length=40 label='1987 Salary',
(select put(count(nHits),best. -l) from have) as nHits length=40 label='Hits in 1986'
from have(obs=1)
union all
select 'Mean(Std)' as statistic length=20,
cats((select put(mean(Salary),12.1 -l) from have),'(',(select put(std(Salary),12.2 -l) from have),')' ),
cats((select put(mean(nHits),12.1 -l) from have),'(',(select put(std(nHits),12.2 -l) from have),')' )
from have(obs=1)
union all
select 'Median' as statistic length=20,
(select put(median(Salary),12.1 -l) from have) ,
(select put(median(nHits),12.1 -l) from have)
from have(obs=1)
union all
select 'Min,Max' as statistic length=20,
cats((select put(min(Salary),best. -l) from have),',',(select put(max(Salary),best. -l) from have) ),
cats((select put(min(nHits),best. -l) from have),',',(select put(max(nHits),best. -l) from have) )
from have(obs=1)
;
quit;`````` 14 REPLIES 14

## Re: SAS macro programme

Before you start to design the macro, you first need to have the non-macro code which gets the result.

Since this is advanced homework, you need to go back to the lessons about the basic SAS statistical procedures and start there.

Once you have your "solve one particular instance" code, post it here, and we can give you hints for the development of your macro.

Keep in mind that homework is meant to test your skills, not ours.

## Re: SAS macro programme

This is what I do, but there are errors

`DATA file;   set SASHELP.BASEBALL;RUN;proc sort data=file; by Salary nHits; run; %MACRO procmean (dataset=, var1=,var2);proc means data=&dataset;var &var1 &var2;output out=filen =   N_&var1 N_&var2mean= M_&var1 M_&var2median= Med_&var1 Med_&var2min=min&var1 min&var2max=max&var1 max&var2run;%MEND procmean;%procmean(dataset=file, var1=Salary, var2=nHits);`

## Re: SAS macro programme

``````proc means data=sashelp.baseball;
var salary nhits;
output ......;
run;``````

For the correct syntax of the OUTPUT Statement, read the linked documentation.

Given your initial assignment, you will need to prepare an intermediate dataset containing salary of one year and nhits of the other; solve this first. Your previous work in the class will have provided you with the necessary skills.

## Re: SAS macro programme

@SASbeginner4 wrote:

This is what I do, but there are errors

`DATA file;   set SASHELP.BASEBALL;RUN;proc sort data=file; by Salary nHits; run; %MACRO procmean (dataset=, var1=,var2);proc means data=&dataset;var &var1 &var2;output out=filen =   N_&var1 N_&var2mean= M_&var1 M_&var2median= Med_&var1 Med_&var2min=min&var1 min&var2max=max&var1 max&var2run;%MEND procmean;%procmean(dataset=file, var1=Salary, var2=nHits);`

As mentioned before, get it working without macro code first. Then you can figure out how to get it working with macro code by replacing parts of the code. Debugging both code and macro at the same time increases the difficulty of the problem significantly.

.

Here are some references for you: Creating complex reports:

Sample code:

https://support.sas.com/rnd/papers/sgf2008/complex_reports.zip

And a really old one I wrote that's more than you need here because it looks at categorical and binary variables.

https://gist.github.com/statgeek/2f27939fd72d1dd7d8c8669cd39d7e67

If you search you'll find many examples online.

## Re: SAS macro programme

I think you will need to read on the two types of macro parameters, i.e Keyword and positional parameters. I have a book that can help you in this.
In this line %MACRO procmean (dataset=, var1=,var2); you have keyword parameters preceding positional parameters, this is wrong, it should be vicevasa.

## Re: SAS macro programme

Given the desired output that you show, you would be wise to look at the %TABLEN macro and use that, rather than write your own.

--
Paige Miller

## Re: SAS macro programme

I suggest you check the assignment.  Are they really asking you create an output DATASET or perhaps a REPORT?  It's not possible to have a dataset with a variable named 1987 Salary, because a SAS variable name cannot start with a number.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

## Re: SAS macro programme

They are the labels, not variable names

## Re: SAS macro programme

@SASbeginner4 wrote:

They are the labels, not variable name

I would still check in with the instructor about the intent of the assignment.  It's unusual to want a dataset with information buried in character values like this.  It's more typical to want that format as a report.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

## Re: SAS macro programme

You cannot have a single variable that is displayed using different formats at different times.  You cannot have a numeric variable that display two different numbers at the same time.

You need to make a CHARACTER variable to hold the values in the cells of your report.

You can use functions to convert the value into strings.

Example:

``cell_value = put(n,2.);``

Will make a string that looks like XX in your first row.  If the numbers can be larger then 99 then you will obviously need to use a width or more than 2.

Other examples:

``````cell_value = put(n,5.);
cell_value = put(mean,6.1)||' ('||put(std,6.2)||')';
cell_value= put(median,6.1);
cell_value = put(min,6.2)||', '||put(max,6.2.);``````  Ksharp
Super User

## Re: SAS macro programme

``````/*
Here is an example.
Good Luck.
*/
data have ;
set SASHELP.BASEBALL;
keep nHits Salary;
run;
proc sql;
create  view want as
select 'N' as statistic length=20,
(select put(count(Salary),best. -l) from have) as salary length=40 label='1987 Salary',
(select put(count(nHits),best. -l) from have) as nHits length=40 label='Hits in 1986'
from have(obs=1)
union all
select 'Mean(Std)' as statistic length=20,
cats((select put(mean(Salary),12.1 -l) from have),'(',(select put(std(Salary),12.2 -l) from have),')' ),
cats((select put(mean(nHits),12.1 -l) from have),'(',(select put(std(nHits),12.2 -l) from have),')' )
from have(obs=1)
union all
select 'Median' as statistic length=20,
(select put(median(Salary),12.1 -l) from have) ,
(select put(median(nHits),12.1 -l) from have)
from have(obs=1)
union all
select 'Min,Max' as statistic length=20,
cats((select put(min(Salary),best. -l) from have),',',(select put(max(Salary),best. -l) from have) ),
cats((select put(min(nHits),best. -l) from have),',',(select put(max(nHits),best. -l) from have) )
from have(obs=1)
;
quit;`````` ## Re: SAS macro programme

Thank you very much.

but can you help me to do it without sql?

I wrote a code, it above this messages, but there are errors.  Ksharp
Super User

## Re: SAS macro programme

``````DATA file;
set SASHELP.BASEBALL;
RUN;
proc sort data=file; by Salary nHits; run;
%MACRO procmean (dataset=, var1=,var2=);
proc univariate data=&dataset. outtable=temp noprint;
var &var1. &var2.;
run;
data temp2(index=(x=(id Statistic)));
set temp;
length Statistic value \$ 40;
id=1;Statistic='N';        value=put(_NOBS_,best. -l);output;
id=2;Statistic='Mean(Std)';value=cats(put(_MEAN_,12.1 -l),'(',put(_STD_,12.2 -l),')');output;
id=3;Statistic='Median';   value=put(_MEDIAN_,12.1 -l);output;
id=4;Statistic='Min,Max';  value=cats(put(_MIN_,best. -l),',',put(_MAX_,best. -l));output;
keep id _VAR_ _LABEL_ Statistic value;
run;
proc transpose data=temp2 out=want(drop=id _name_);
by id Statistic;
var value;
id _VAR_;
idlabel _LABEL_;
run;
%MEND procmean;

%procmean(dataset=file, var1=Salary, var2=nHits);
``````

## Re: SAS macro programme

Thank you very much!!!

Discussion stats
• 14 replies
• 651 views
• 6 likes
• 8 in conversation