topic Re: Proc Means loop Macro in SAS Programming

Proc Means loop Macro

Ujjawal — Sun, 13 Jul 2014 10:35:33 GMT

I am trying to create a dynamic macro that loops through CLASS variables and generate different output dataset for each of classification variables.

The following is the program i am working on. It does not loop through CLASS variables. :-

Data test;

Input Q1 Q2 Q3 Q4 Q5 Age BU;

cards;

1 5 2 3 4 3 3

4 5 2 1 2 1 3

3 4 4 3 2 3 2

4 3 2 5 3 3 3

1 2 4 2 1 2 2

;

Run;

%macro summary (inputdata= , vars= , groups=, outputdata=);

Proc Means data = &inputdata nway noprint;

class &groups;

var &vars

output out=&outputdata (rename=(_freq_=N) drop = _type_) mean= median= / autoname;;

run;

%mend;

%summary(inputdata=test, vars = Q1-Q5, groups=Age BU, outputdata=file1);

What i want :-

1. One output file for numeric variables (Q1-Q5) by Age.

2. One output file for numeric variables (Q1-Q5) by BU.

The macro should not be restricted to 2 classification variables. If the number of classification variables (CLASS) increases, it should generate files for each of the classification variables.

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 11:20:39 GMT

Isn't is simpeler to have proc means generated all levels for Age and BU and then do a select on what you are needing?

Assuming Age and BU join do not generate a lot of cells you cand run it using the "in memory" approach " not needing the input dataset to be sorted.

Re: Proc Means loop Macro

data_null__ — Sun, 13 Jul 2014 12:30:59 GMT

What classification crossing do you want? PROC MEANS has statements to specify the TYPES or WAYS that you are interested in. TYPES has a syntax similar to PROC FREQ table statement.

You can have hundreds of CLASS variables as long as you restrict the OUTPUT with TYPES or WAYS to something reasonable

You don't want a loopy macro for this.

Re: Proc Means loop Macro

Ujjawal — Sun, 13 Jul 2014 13:05:12 GMT

Thank you for your reply. I want output in separate tables. I am a novice in macro programming. I am unable to apply loop in this situation.

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 13:19:50 GMT

You do not need to apply a macro loop as the better solution for your issue is thinking in other way.

Re: Proc Means loop Macro

data_null__ — Sun, 13 Jul 2014 13:21:51 GMT

Explain separate tables, in detail with example.

Re: Proc Means loop Macro

Ujjawal — Sun, 13 Jul 2014 13:31:57 GMT

When i say "Separate tables", i mean two output files - one for Age*(Q1-Q5) and second for BU*(Q1-Q5). I don't want output in a single file.

I want the following program automated. Everything is same in the code except CLASS variable and OUT dataset.

Proc Means data = input nway noprint;

class Age;

var Q1-Q5;

output out=data1 (rename=(_freq_=N) drop = _type_) mean= median= / autoname;;

run;

Proc Means data = input nway noprint;

class BU;

var Q1-Q5;

output out=data2 (rename=(_freq_=N) drop = _type_) mean= median= / autoname;;

run;

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 13:42:53 GMT

You can split up that single file delivered by proc means more easy to your needs eg using a SAS datastep, this is a common solution.

Why this:
- proc means will access the input dataset needing calcualtions

You only need to access all input data once not looped, to create that dataset having all type nway information..

- using a sas datastep splitting up in many sas datsets is a feature also needing reading once the input but support also a split up in one run.

This is a very efficient way of splitting up when needed.
In this case see the resulting dataset of proc means as intermediate result for a solution having more steps.

For your information this is part of the SAS advanced course where thinking on effects on performance is coming into the topics.
A lot of people are getting into trouble when data is somewhat more big as just the tiny samples. The Cartesian product warning is a famous one.

Re: Proc Means loop Macro

slchen — Sun, 13 Jul 2014 13:51:30 GMT

%macro summary (inputdata= , vars= , groups=, outputdata=);

%let i=1;

%do %while (%scan(&groups,&i) ne );

%let group=%scan(&groups,&i);

Proc Means data = &inputdata nway noprint;

class &group;

var &vars;

output out=&outputdata.&i (rename=(_freq_=N) drop = _type_) mean= median= / autoname;;

run;

%let i=%eval(&i+1);

%end;

%mend;

%summary(inputdata=test, vars =Q1-Q5, groups=Age BU, outputdata=file);

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 14:31:25 GMT

run this code, look at tstint, you will notice _type_ is having the selection of the needed class specification 10 or 01 is according the order in the class statement
When you need to split up further a macro on the class definition using a datastep split is possible ... joining and combining in next steps preparing for other analytics.

proc Means data = test noprint chartype ;
class bu age;
var q1 - q5 ;
output out=testint mean= median= / autoname;
run;

Re: Proc Means loop Macro

Ujjawal — Sun, 13 Jul 2014 15:28:12 GMT

@Slchen : Thanks a bunch for your program. It works like a charm.

@Jaap : Thanks a ton. I know your solution is efficient. It's just i want the tables separately.

I explore CHARTYPE option. I am not sure how CHARTYPE is useful. I googled it. Many articles say " It helps you to output multiple datasets". This can be done without using CHARTYPE option. See the code below -

proc Means data = test noprint;

class bu age;

var q1 - q5 ;

output out=F1(where=(_type_=1) drop= BU _FREQ_) mean= median= / autoname;

output out=F2(where=(_type_=2) drop= AGE _FREQ_) mean= median= / autoname;

output out=F3(where=(_type_=3) drop= _FREQ_) mean= median= / autoname;

;

run;

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 16:06:21 GMT

I used the chartype because it gets a binary 0/1 indication on each class variable. With this one it should be always possible to select on each class combination.
The coding is however more cumbersome to achieve there is more easier approach when you want just one of each class variable.

Proc measn has evolved a lot see: Base SAS(R) 9.4 Procedures Guide, Second Edition threads (multithreading) memory and all kind of others. Interesting the clasdata and types.
Adding types after class is making a sas dataset where each class variable is not missing as like al your wanted datasets are appended.

proc Means data = test noprint chartype ;
class bu age;
types bu age;
var q1 - q5 ;
output out=testint mean= median= / autoname;
run;

Splitting up now is more easy

%macro splitmean (inp= , groups= ) ;

/* build all output datasets as splitter */
data
%let i=1; %let group=%scan(&groups,&i);
%do %while (%length(&group) > 0 );
mnspl_&group
%let i=%eval(&i+1); %let group=%scan(&groups,&i);
%end;
;
set &inp (drop=_type_) ;

/* redirect each record to a dedicated output dataset */
Select ;
%let i=1; %let group=%scan(&groups,&i);
%do %while (%length(&group) > 0 );
When ( &group ne . ) output mnspl_&group ;
%let i=%eval(&i+1); %let group=%scan(&groups,&i);
%end;
Otherwise ;
end;

run;
%mend ;

%splitmean (inp=testint, groups=bu age) ;

Re: Proc Means loop Macro

jakarman — Sun, 13 Jul 2014 16:20:19 GMT

I think proc means will run once on the input datasets but having multiple ouput statements (reports?) need to reseve space multiple time in memory for all calculations.
There are always a lot of approaches and solutions with SAS but not all of them are performing or having the resources usage being used efficiency equal.