Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- SAS macro programme

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-28-2023 03:01 AM
(650 views)

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

- Create a macro program to calculate N, Mean, Std, Median, Min and Max for SALARY and for NHITS(SASHELP.BASEBALL)
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
/*
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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*.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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=file

n = N_&var1 N_&var2

mean= M_&var1 M_&var2

median= Med_&var1 Med_&var2

min=min&var1 min&var2

max=max&var1 max&var2

run;

%MEND procmean;

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Start without any macro elements:

```
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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=file

n = N_&var1 N_&var2

mean= M_&var1 M_&var2

median= Med_&var1 Med_&var2

min=min&var1 min&var2

max=max&var1 max&var2

run;

%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:

Paper: https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/173-2008.pdf

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

In this line %MACRO procmean (dataset=, var1=,var2); you have keyword parameters preceding positional parameters, this is wrong, it should be vicevasa.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

They are the labels, not variable names

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
/*
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you very much!!!

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.