SAS macro programme

[SASbeginner4]

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

   Thank you for your help.

[Kurt_Bremser]

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.

[SASbeginner4]

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

[Kurt_Bremser]

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.

[Reeza]

---

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

[himself]

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.

[PaigeMiller]

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.

[Quentin]

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.

[SASbeginner4]

They are the labels, not variable names

[Quentin]

---

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

[Tom]

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 · Accepted solution]

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

[SASbeginner4]

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]

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

[SASbeginner4]

Thank you very much!!!