topic Re: SAS macro programme in SAS Programming

SAS macro programme

SASbeginner4 — Wed, 28 Jun 2023 07:01:47 GMT

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

Thank you for your help.

Re: SAS macro programme

Kurt_Bremser — Wed, 28 Jun 2023 07:59:12 GMT

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

SASbeginner4 — Wed, 28 Jun 2023 08:34:47 GMT

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

Re: SAS macro programme

PaigeMiller — Wed, 28 Jun 2023 09:27:33 GMT

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.

Re: SAS macro programme

Kurt_Bremser — Wed, 28 Jun 2023 10:45:36 GMT

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.

Re: SAS macro programme

Quentin — Wed, 28 Jun 2023 11:48:26 GMT

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.

Re: SAS macro programme

SASbeginner4 — Thu, 29 Jun 2023 21:49:21 GMT

They are the labels, not variable names

Re: SAS macro programme

Tom — Thu, 29 Jun 2023 22:03:06 GMT

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

Re: SAS macro programme

Reeza — Thu, 29 Jun 2023 22:43:46 GMT

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

Re: SAS macro programme

Ksharp — Fri, 30 Jun 2023 11:37:42 GMT

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

Quentin — Fri, 30 Jun 2023 13:09:56 GMT

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

Re: SAS macro programme

himself — Fri, 30 Jun 2023 13:37:18 GMT

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

SASbeginner4 — Mon, 03 Jul 2023 08:28:02 GMT

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.

Re: SAS macro programme

Ksharp — Mon, 03 Jul 2023 11:47:26 GMT

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

SASbeginner4 — Tue, 04 Jul 2023 06:03:16 GMT

Thank you very much!!!

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

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