topic Re: SAS's Macro: Create dataset with several dataset one observation-one variable in SAS Enterprise Guide

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

luciacossaro — Mon, 13 Aug 2018 06:14:38 GMT

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,1	Dataset 1,2	Dataset 1,3	….	Dataset 1,i
Mean_11	Mean_12	Mean_13		Mean_1i
5,5	8	45		12

J=2
Dataset 2,1	Dataset 2,2		….	Dataset 2,i
Mean_21	Mean_22	Mean_23		Mean_2i
87	85	4		65
...
...
J=j
Dataset j,1	Dataset j,2		….	Dataset j,i
Mean_j1	Mean_j2			Mean_ji
15	5			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_11	Mean_12	Mean_13		Mean_1i
5,5	8	45		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 !

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

RW9 — Mon, 13 Aug 2018 08:08:06 GMT

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;

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

luciacossaro — Mon, 13 Aug 2018 08:47:55 GMT

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_1	Mean_2	Mean_3
87	98,5	99,5

it is possible to do this with PROC MEANS ?

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

RW9 — Mon, 13 Aug 2018 09:29:20 GMT

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;

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

ballardw — Mon, 13 Aug 2018 15:52:15 GMT

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.