%Macro

[karachi007]

I  have 2 FILES (FILE1, FILE2) how I read two SAS data sets in my PROC SQL and create two tables for each dataset. I want sepearate output for each file

something like this:

%MACRO FILE (FILE1, FILE2);

proc sql create table tbl.&FILEOUT.

select * from work.&FILE.

; run;

%MEND FILE (FILE1,FILEOUT1,  FILE2, FILEOUT2)

quit;

Thank you

[Astounding]

You really do have an impossible task here.  How do you write a macro to utilize SQL, when you don't know macro language and you don't know SQL?  How do you even communicate what is needed?

I suggest you write a piece of this with no macro language at all.  Just get the SQL code working for one file, so you can illustrate what you are trying to do.  At that point, you can probably get some help in completing the task.

[karachi007]

sql is very simple: :

create table out.file1;

select * from file1; run;

create table out.file2

select * from file2; run;

how I write a %macro for file 1 and file 2 as well out.file 1 and out.file2?

%MACRO FILE (FILE1, FILE2);

%MEND (FILE1,FILE2)

[karachi007]

I want to create separate datasets each file

[Tom]

First step is write code that can run.

proc sql;
create table out.FILE1 as
  select * from FILE1
; 
create table out.FILE2 as
  select * from FILE2
; 
quit;

When that works then the next step is take your working code and replace the variable parts with macro variable references. Use %LET statement to set the values.

%let ds1=FILE1;
%let ds2=FILE2;

proc sql;
create table out.&ds1. as
  select * from &ds1.
; 
create table out.&ds2. as
  select * from &ds2.
; 
quit;

If that works then you can create a macro.

%macro copyfiles(ds1,ds2);
proc sql;
create table out.&ds1. as
  select * from &ds1.
; 
create table out.&ds2. as
  select * from &ds2.
; 
quit;
%mend copyfiles;

And then you can call the macro:

%copyfiles(ds1=FILE1,ds2=FILE2)

Note that if that is what you want to do your macro can generate much simpler code.

%macro copyfiles(ds1,ds2);
proc copy inlib=work outlib=out;
  select &ds1. &ds2. ;
run;
%mend copyfiles;

And you could make it more flexible by using a single parameter that can take a space delimited list of member names.

%macro copyfiles(dslist);
proc copy inlib=work outlib=out;
  select &dslist. ;
run;
%mend copyfiles;
%copyfiles(FILE1 FILE2);

[karachi007]

Thanks Tom.

This is not what I want all I want

one proc sql execute first with  file1 and then file2 and create two separate output for file 1 and file 2 is kind of loop using macro in one sql

[Tom]

We cannot read your mind.
If you cannot write the code then explain what you want in words. Provide example input data and the expected output for that input data.

[Kurt_Bremser]

---

@karachi007 wrote:

sql is very simple: :

create table out.file1;

select * from file1; run;

create table out.file2

select * from file2; run;

how I write a %macro for file 1 and file 2 as well out.file 1 and out.file2?

%MACRO FILE (FILE1, FILE2);

%MEND (FILE1,FILE2)

---

It might be very simple for us, but your code shows clearly that it is absolutely NOT simple for you, as it is just a sequence of syntax errors.

So I highly recommend you work through the freely available online Programming 1 course first to get a grasp of the basics of SAS.

We can (and are glad to) help you with SAS, but teaching SAS in the first place is another matter.

Once you have reached the stage where you can write the complete SQL procedure step for one instance, and get it to run error-free, we can show you how to convert it to dynamic code.

If it was just that you were very sloppy in posting your code, then please post your real, working code for one instance here.

[karachi007]

Tom thanks for your response.

Just need to run my code using %macro with two data sets like in the loop

[Patrick]

@karachi007 

SAS Macro coding is great for creating dynamic code. But SAS Macro coding is also a recipe to create seriously convoluted and hard to read code. Beginners tend to get too early into SAS Macro coding and often end up with very bad designs and bad code.

The details of the questions you ask and the level of code you post indicate that you're a beginner. I'd recommend that you read carefully what some of the senior people here are trying to tell you and how they are trying to guide you to a more "best practice" design and code.

But as you insist here what I believe you're asking for.

%macro test(in1, out1, in2, out2);
  proc sql;
    create table &out1 as
      select * from &in1
    ;
    create table &out2 as
      select * from &in2
    ;
  quit;
%mend;

%test(sashelp.class, work.class, sashelp.air, work.air)

[karachi007]

No resolution for my problem
All I need one sql read two files one at a time and create output and reruns sql with file2 and create 2nd output. All I need macro to run the logic

[Tom]

---

@karachi007 wrote:
No resolution for my problem
All I need one sql read two files one at a time and create output and reruns sql with file2 and create 2nd output. All I need macro to run the logic

---

Please explain what you want to do. Show the code that you need to run.  You cannot use a macro to create code until you know what code you want the macro to create.

Why do you think that you need a macro to do what you want to do? Do you plan to do it again at some point?  If so what will be different about the future runs?

[Tom]

---

@karachi007 wrote:
No resolution for my problem
All I need one sql read two files one at a time and create output and reruns sql with file2 and create 2nd output. All I need macro to run the logic

---

Are you just asking how to loop over a list of dataset names?

%macro do_all(dslist);
%local i next;
%do i=1 %to %sysfunc(countw(&dslist,%str( )));
  %let next=%scan(&dslist,&i,%str( ));
   .... code that references macro variable &NEXT ....
%end;
%mend do_all;
%do_all(file1 file2 anotherfile)

[karachi007]

Tom,
I have a code like this:
Proc sql;
connect to database
create table test.&outputfile. as
select * from
work.&file1. a
Left join oracletable b
On a.id =b.id

I need to run same sql with File1,,,File10
and create 10 output datasets for each file
I will be running this code 100 times
with multiple databases
That’s why I need to use macro
Hope you understand the logic now

[Tom]

---

@karachi007 wrote:
Tom,
I have a code like this:
Proc sql;
connect to database
create table test.&outputfile. as
select * from
work.&file1. a
Left join oracletable b
On a.id =b.id

I need to run same sql with File1,,,File10
and create 10 output datasets for each file
I will be running this code 100 times
with multiple databases
That’s why I need to use macro
Hope you understand the logic now

---

Follow the steps in my first post on this thread to write a macro to implement that code for one input/output pair.

Then either call the macro many times.  

Or better get your list of 100's of datasets into a dataset and use a data step it to generate multiple calls to the macro.  You can use CALL EXECUTE() or simple write the macro calls to a text file use PUT statement and then use %INCLUDE to run the generated calls.