BookmarkSubscribeRSS Feed
Ujjawal
Quartz | Level 8

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.

12 REPLIES 12
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
data_null__
Jade | Level 19

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.

Ujjawal
Quartz | Level 8

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.

data_null__
Jade | Level 19

Explain separate tables, in detail with example.

Ujjawal
Quartz | Level 8

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;

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
slchen
Lapis Lazuli | Level 10

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

jakarman
Barite | Level 11

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;

---->-- ja karman --<-----
Ujjawal
Quartz | Level 8

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

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
jakarman
Barite | Level 11

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

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4970 views
  • 0 likes
  • 4 in conversation