Desktop productivity for business analysts and programmers

SAS's Macro: Create dataset with several dataset one observation-one variable

Reply
Contributor
Posts: 34

SAS's Macro: Create dataset with several dataset one observation-one variable

Hi !

 

I need to create several datasets with a SAS’s Macro but I could not do it.

I have a dataset with “I” variables group by variable “j” and i get many statistics for each one with Proc Means.

At the end, I have for each variable j value,  i datasets with one variable and one observation, like this :

 

J=1    
Dataset 1,1Dataset 1,2Dataset 1,3….Dataset 1,i
Mean_11Mean_12Mean_13 Mean_1i
5,5845 12
     
J=2    
Dataset 2,1Dataset 2,2 ….Dataset 2,i
Mean_21Mean_22Mean_23 Mean_2i
87854 65
 ...    
 ...    
J=j    
Dataset j,1Dataset j,2 ….Dataset j,i
Mean_j1Mean_j2  Mean_ji
155  

23

 

I need to create only one table with all  of one variable’s datasets for each j value group.

Something like that :

Dataset j=1,

Mean_11Mean_12Mean_13 Mean_1i
5,5845 12

 

 

I tried that but it do not work:

 

%let j=1 ; 
%macro all;
data test_t&j ; 
	set 
	%do i=1 %to 10 ; 
	 mean_&j&i sum_&j&i 
	%end ; 
run ; 
%mend ; 
%all;

Could somebody help me please to find where I have wrong ? There is a way to do a DO loop for the value of j to avoid making a macro for each value of j ?

 

Thank you very much !

 

Super User
Super User
Posts: 9,840

Re: SAS's Macro: Create dataset with several dataset one observation-one variable

Posted in reply to luciacossaro

I do not understand your question.  Present test data in the form of a datastep, and show what the output should be.  Can you not add another do loop?

%macro all (j=);
  %do e=&j. %to 10;
    data test_t&j ; 
	set 
	%do i=1 %to 10 ; 
	   mean_&j&i sum_&j&i 
	%end ; 
    run ; 
  %end;
%mend ; 
%all (j=1);

However, that being said, this is bad idea.  It is rarely a good idea to split data up into lots of small datasets  - just this bit of code you present shows how much more difficult programming with such a thing is.  SAS is built on the fundamental concept of by group processing, i.e. add groupings to your data then and then use those in one dataset with one procedure for maximum speed and resource usage.  For instance, say I wanted to do a proc means on ages for M or F in sashelp.class, I could spit them into two - taking more disk space, and doubling the processing time, and having messy code, or I could do:

proc means data=sashelp.class;
  by sex;
  var age;
run;
Contributor
Posts: 34

Re: SAS's Macro: Create dataset with several dataset one observation-one variable

Thanks for the reply. Of course, if I can obtain the results in a single table with PROC MEANS, it would be better; but I do not know if it is possible. I have a database with the variables j, i and another variable X (numeric). Is there any way to obtain with PROC MEANS a database for each value of J that contains i variables equal to the average of X for i = 1,2,3 ....?

 

 

DATA test ; 
input j i X ;
datalines ;
1 1 85
1 2 96
1 3 100
2 1 6
2 2 8
2 3 9
3 1 12
3 2 15
3 3 18
4 1 6
4 2 5
4 3 8
1 1 89
1 2 101
1 3 99
2 1 7
2 2 5
2 3 6
3 1 15
3 2 19
4 1 4
4 2 1
4 3 2
;
run ;
proc sort data=test;
by j i ;
run ;

I would like to obtain for each value j = 1,2,3,4 a database with averages such as for j = 1 for exemple:

j=1  
Mean_1Mean_2Mean_3
8798,599,5

 

it is possible to do this with PROC MEANS ?

Super User
Super User
Posts: 9,840

Re: SAS's Macro: Create dataset with several dataset one observation-one variable

Posted in reply to luciacossaro

What groupings are we talking here, all j together, all i together?  Something like this, but just need to set groupings:

data test; 
  input j i x; 
datalines; 
1 1 85
1 2 96
1 3 100
2 1 6
2 2 8
2 3 9
3 1 12
3 2 15
3 3 18
4 1 6 
4 2 5 
4 3 8
1 1 89
1 2 101
1 3 99
2 1 7
2 2 5
2 3 6
3 1 15
3 2 19
4 1 4 
4 2 1 
4 3 2
; 
run;
data want;
  set test;
  by j notsorted;
  retain tot_sum num_elem;
  if first.j then do;
    tot_sum=x;
    num_elem=1;
  end;
  else do;
    tot_sum=sum(tot_sum,x);
    num_elem=sum(num_elem,1);
  end;
  if last.j then do;
    mean=tot_sum / num_elem;
    output;
  end;
run;
Super User
Posts: 13,941

Re: SAS's Macro: Create dataset with several dataset one observation-one variable

Posted in reply to luciacossaro

Please consider:

DATA test ; 
 input j i X ; 
 datalines ; 
1 1 85
1 2 96
1 3 100
2 1 6
2 2 8
2 3 9
3 1 12
3 2 15
3 3 18
4 1 6 
4 2 5 
4 3 8
1 1 89
1 2 101
1 3 99
2 1 7
2 2 5
2 3 6
3 1 15
3 2 19
4 1 4 
4 2 1 
4 3 2
; 
run ; 

proc summary data=test;
   class j i;
   var x;
   output out=summary mean= std=/autoname;
run;

I added another statistic std just to show the possibility.

 

The output data set summary will have summaries (mean and std) of x for all of the records, each value of I, each value of j and combinations of j and I. There is a special variable in the data named _type_ that you can use to indicate which specific summaries you want. Alternatively there are options using the TYPES and WAYS options to build desired combinations of variables on the CLASS statement.

 

Proc Summary and Means share options, I just prefer summary.

Ask a Question
Discussion stats
  • 4 replies
  • 88 views
  • 0 likes
  • 3 in conversation