topic Re: %Macro in SAS Enterprise Guide

%Macro

karachi007 — Tue, 28 May 2019 23:42:29 GMT

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

Re: %Macro

Astounding — Wed, 29 May 2019 02:30:54 GMT

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.

Re: %Macro

karachi007 — Wed, 29 May 2019 02:43:06 GMT

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)

Re: %Macro

karachi007 — Wed, 29 May 2019 03:00:19 GMT

I want to create separate datasets each file

Re: %Macro

Tom — Wed, 29 May 2019 03:07:23 GMT

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);

Re: %Macro

karachi007 — Wed, 29 May 2019 03:24:49 GMT

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

Re: %Macro

Tom — Wed, 29 May 2019 04:30:13 GMT

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.

Re: %Macro

Kurt_Bremser — Wed, 29 May 2019 06:37:35 GMT

@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.

Re: %Macro

karachi007 — Wed, 29 May 2019 10:42:56 GMT

Tom thanks for your response.

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

Re: %Macro

Patrick — Wed, 29 May 2019 11:52:02 GMT

@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)

Re: %Macro

karachi007 — Wed, 29 May 2019 15:45:13 GMT

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

Re: %Macro

Tom — Wed, 29 May 2019 15:58:16 GMT

@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?

Re: %Macro

Tom — Wed, 29 May 2019 16:19:22 GMT

@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)

Re: %Macro

karachi007 — Wed, 29 May 2019 16:36:08 GMT

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

Re: %Macro

Tom — Wed, 29 May 2019 18:00:56 GMT

@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.

Re: %Macro

karachi007 — Wed, 29 May 2019 23:30:50 GMT

Tom, No luck

this is my code:

%macro test(DSN):

proc sql;

create connection to ORACLE ,,,,,,,

create table employee_&DSN. as

select * from .&DSN. a

left join employee e

on a.id = e.id;

run;

%mend test;

%test(FILE1,FILE2)

quit;

getting errors:

more positional parameters found than defined

please advise and let me know the issue. Thank

Re: %Macro

Patrick — Sun, 02 Jun 2019 03:02:51 GMT

@karachi007

It is a common beginners problem to try and use SAS Macro language for a problem that should be addressed differently. It's much better to first better skill up in normal SAS language before even attempting to use SAS Macro language.

I believe we could give you better advice if you can tell us what bigger problem you want to solve. Share the bigger picture with us? What do you have to start with, what do you need as target state and where you're stuck and need help with? - A description of the problem like: A bunch of SAS tables in a source library to be loaded into same named tables in Oracle. Dynamic code required to just pick-up whatever tables are in source. Succeeded to write the load code for a single table but don't manage to make the code dynamic.

Re: %Macro

Kurt_Bremser — Sun, 02 Jun 2019 15:32:01 GMT

@karachi007 wrote:

Tom, No luck

this is my code:

%macro test(DSN):

proc sql;

create connection to ORACLE ,,,,,,,

   create table employee_&DSN. as



select * from .&DSN. a

   left join employee e

   on a.id = e.id;

run;

%mend test;

%test(FILE1,FILE2)

quit;

getting errors:

more positional parameters found than defined

please advise and let me know the issue. Thank

The ERROR message is very clear and obvious: you defined the macro with only ONE parameter, and then tried to call it with TWO parameters. This is not possible.