I'm not 100% sure here, but I never use SAS SQL code on other SQL platforms. To be more precise, in SAS SQL you can create tables "on the fly" by using the "create table foo as ..." syntax but I think this is only a convenient SAS structure which will not translate over to other SQL platforms like microsoft visual studio (although it may for some, I don't know). Although I thought this was a SAS forum, I will propose the following solution because the syntax below is also allowed in SAS SQL although sometimes unnecessary: FIrst create a blank shell for your table using: create table B      (      date as date,      xx as float,      yy as float      ) ; Notice that there is no "as" after the create statement! Then insert into that table the values you want: insert into B      select date, sum(x) as xx,sum(y)  as yy      from table A      group by date ; You should note that some things may change depending on the syntax of SQL in microsoft visual studio... For instance the the round parenthases, (),  may be curly brackets, {}.And the declaration of "date as date" may require a different data type. I haven't used SQL in visual studio so I can't give you any exact code, but I think this will get you on your way... Message was edited by: never aragafi Notice that there is no "as" after the create statement! ... View more

Shoot, I thought about that too but the results didn't change for me. I'm using sas on UNIX, could that have anything to do with it? ... View more

Does it work if you run the macro over two data sets rather than one? Extending your code, for example: *Create a temp data that I will use to insert; data t1;     do i = 1 to 10;         diagnosis1 = i;         diagnosis2 = i-2;         diagnosis3=5;     output;     end; run; *Create a second temp data that I will use to insert; data t3;     do i = 1 to 10;         diagnosis1 = i+6;         diagnosis2 = i-12;         diagnosis3= .;         diagnosis4 = i*i ;     output;     end; run; *the data I want to insert into; data t2;     do i = 1 to 20;         diagnosis = i;     output;     end; run; options mprint mlogic symbolgen; %macro loop(file, count) ;                 %do i = 1 %to &count. ;                 proc sql ;                         insert into work.t2                         select  i as personid, diagnosis&i. as diagnosis                         from &file                 where  diagnosis&i. <> . ;                quit ;                 %end ; %mend loop ; %loop(t1, 3) ; %loop(t3, 4) ; ... View more