sas macro in proc sql

1
votes

i have a problem that probably very simple but i can't figured out.

i have columns name1 c1 c2 c3 c4 .... c8 i have to sum by names these columns and make a new data.

Here is the code that i wrote but it didnt work.

Could you please help me?

PROC SQL;
   CREATE TABLE WORK.data1 AS 
   SELECT t1.name1, 

%let k=8
%macro test;
%do i=0 %to &k;
%sysfunc(SUM(C&i)) AS C&i;
%END;
%mend test;
%test;
      FROM WORK.data t1
      GROUP BY t1.name1,
QUIT;
2
sqlmacrossasproc
And maybe have a look at this paper: www2.sas.com/proceedings/sugi29/243-29.pdf – Michael Kersten
ok i will study this. thank you very much – user3375397
No problem :) Hint: Missing semicolon after %let and use of sysfunc at this point is not necessary. And you can use mprint in combination with mfile to get a file containing the base code wich is generated by the macro code. Very useful for debuging and learning. – Michael Kersten
i am very new and realy feel like in an ocean :). mprint and mfile in my search list now. thank you. – user3375397
Yeah, SAS is a little bit confusing in the beginning, but its a really great tool for certain tasks. – Michael Kersten

2 Answers

3
votes
data data;
input name1 $ c1 c2 c3;
datalines4;
a 1 2 3
a 1 2 3
b 1 2 3
b 1 2 3
;;;;
run;

%macro test(k=);
  PROC SQL;
   CREATE TABLE WORK.data1 AS 
   SELECT t1.name1 

%do i=1 %to &k.;
   , SUM(C&i.) AS C&i.
%END;

  FROM WORK.data t1
  GROUP BY t1.name1;
QUIT;

%mend test;
%test(k=3);
1
votes

This seems a convoluted way of achieving your result, PROC SUMMARY can do it much easier utilising the colon modifier to sum all variables beginning with C. The code below runs off the dataset created by @Mike and produces identical output.

proc summary data=data nway;
class name1;
var c: ;
output out=want (drop=_:) sum=;
run;