Create Table with Loop in SAS

Question

When you have a table like;

somefield1 somefield2 lastterm1 lastterm2
(10) (20) (1) (-)
(20) (40) (1) (2)
(30) (50) (-) (2)

and try to group for each term, is there an easier way to do it rather than;

PROC SQL;
    CREATE TABLE WORK.LAST_TERM1 AS 
    SELECT T1.LASTTERM1,
    (MEAN(t1.'somefield1'n)) FORMAT=COMMAX14.2 AS 'MEAN_of_somefield1'n,
    (MEAN(t1.'somefield2'n)) FORMAT=COMMAX14.2 AS 'MEAN_of_somefield2'n

    FROM WORK.MAIN_TABLE t1
GROUP BY t1.LASTTERM1;
RUN;
PROC SQL;
    CREATE TABLE WORK.LAST_TERM2 AS 
    SELECT T1.LASTTERM2,
    (MEAN(t1.'somefield1'n)) FORMAT=COMMAX14.2 AS 'MEAN_of_somefield1'n,
    (MEAN(t1.'somefield2'n)) FORMAT=COMMAX14.2 AS 'MEAN_of_somefield2'n

    FROM WORK.MAIN_TABLE t1
GROUP BY t1.LASTTERM2;
RUN;

It's not a problem with two fields and two terms, but when you have 20 terms and 200 fields this code is not effective.

I tried and failed to do something like for 1 to 20 do this and create all tables.

Is there any way to do this?

JJFord3 · Accepted Answer · 2016-11-10T17:42:22.123

0

I think you are looking for proc means. You can loop through the list of grouping variables (i.e. lastterm1, lastterm2) with a sas-macro replacing the name of the output table and class variable and should have everything you need.

This is a simple macro that will do it for last_term1 - last_term20. Its a little unclear exactly what you want so this is a basic generalized form.

%macro looper();
%do i = 1 %to 20;
    proc means data=WORK.MAIN_TABLE;
    var somefield1 somefield2;
    class lastterm&i.;
    output out=WORK.LAST_TERM&i.
      mean(somefield1) = MEAN_of_somefield1
      mean(somefield2) = MEAN_of_somefield2;
    run;
%end;
%mend;
%looper();

edited Nov 10 '16 at 17:42

answered Nov 09 '16 at 14:03

JJFord3

1,976
1
25
40

Actually I am asking how to do the sas-macro which can replace output table name and class variable. that is where I failed. It should be done because they are increasing in order, like "lastterm(n)", "lastterm(n+1)"... – kutayatesoglu Nov 10 '16 at 07:37
That macro is completely what I was looking for. Thanks for the help :) – kutayatesoglu Nov 11 '16 at 08:26

Create Table with Loop in SAS

1 Answers1