How can I create table by using macro with multiple variable?

[Witch_Kwang]

Hello!

I'm new with SAS program and quite confuse with the code. I have a problem with macro function and not sure that sas can work as I want.

I want to create many tables with different conditions by creating each table from their value in symbol, Date and interval.

(each table should to be named from their determined condition)

So, I try the following code but it doesn't work.

%macro test;

PROC SQL;

   CREATE TABLE &sym&dd&int AS

   SELECT t1.Symbol,     
          t1.Date,

          t1.Time,        
          t1.interval

        FROM E9count t1

        WHERE t1.Symbol = "&sym";

                    t1.Date= "&dd";

                   t1.interval="&int"

  QUIT;

%mend test;

%let sym=A %let dd=16/04/2009 %let int=1; %test

%let sym=A %let dd=16/04/2009 %let int=2; %test

%let sym=B %let dd=01/05/2009 %let int=1; %test

I'm sure that I miss somthing but don't know how to fix it. Thx in advance.

[JerryLeBreton]

There are lots of problems here! 

First is missing semi-colons (;), and superfluous ones too.

Second, a '/' is not a valid character in a dataset name.

Thirdly, the WHERE clause is missing the (presumed) ANDs.

Forthly, not knowing the data types of variables date and interval, makes a solution guess work.

Assuming that &sec (in the table name) is meant to be &sym and if date is a character variable formatted as '16/04/2009' and interval is character then...

%macro test;

PROC SQL;

   CREATE TABLE &sym%sysfunc(compress(&dd,/))&int AS

   SELECT t1.Symbol,    

          t1.Date,

          t1.Time,       

          t1.interval

        FROM E9count t1

        WHERE t1.Symbol = "&sym"

           and  t1.Date= "&dd"

           and  t1.interval="&int";

  QUIT;

%mend test;

%let sym=A; %let dd=16/04/2009; %let int=1; %test;

%let sym=A; %let dd=16/04/2009; %let int=2; %test;

%let sym=B; %let dd=01/05/2009; %let int=1; %test;

On the other hand, if Date is a SAS date value and Interval is numeric too, then...

%macro test;

PROC SQL;

   CREATE TABLE &sym%sysfunc(compress(&dd,/))&int AS

   SELECT t1.Symbol,    

          t1.Date,

          t1.Time,       

          t1.interval

        FROM E9count t1

        WHERE t1.Symbol = "&sym"

           and  t1.Date= %sysfunc(input(&dd, ddmmyy10.))

           and  t1.interval=∫

  QUIT;

%mend test;

%let sym=A; %let dd=16/04/2009; %let int=1; %test;

%let sym=A; %let dd=16/04/2009; %let int=2; %test;

%let sym=B; %let dd=01/05/2009; %let int=1; %test;

Note: these suggestions have not been tested, but should be closer to working than the original code.

[LinusH]

What is it that does not work? If you get an error, attach the log, otherwise describe in more detail your problem, providing some sample data might help.

Can't see that &sec is defined in your code, perhaps it's done earlier?

[jakarman]

Do not try to fix SAS in a way you want. Try to understand the concepts of SAS and use them in your advantage.

SAS Product Documentation  (look at the related manuals) go for the concepts data management etc. Sorry for pointing at this.

SAS programming is quite different compared to 3GL languages. Perhaps having some RPG background could help.

The major difference is the automatic processing of records.    

The approach of generating many datasets of one(1) origin table is easier and less resource consuming with SAS - datasets.

[Reeza]

I think what is trying to say, is you may want to look into BY GROUP processing for SAS before you start splitting your data into multiple datasets.

It's a generally faster, easier to program method and quite intuitive once you learn about it .