topic Re: How can I create table by using macro with multiple variable? in SAS Data Management

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

Witch_Kwang — Wed, 06 Nov 2013 09:30:46 GMT

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.

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

JerryLeBreton — Wed, 06 Nov 2013 12:14:20 GMT

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.

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

LinusH — Wed, 06 Nov 2013 12:14:21 GMT

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?

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

jakarman — Wed, 06 Nov 2013 20:07:14 GMT

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.

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

Reeza — Wed, 06 Nov 2013 20:13:42 GMT

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 .