BookmarkSubscribeRSS Feed
Witch_Kwang
Calcite | Level 5

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.

4 REPLIES 4
JerryLeBreton
Pyrite | Level 9

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
Tourmaline | Level 20

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?

Data never sleeps
jakarman
Barite | Level 11

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.


---->-- ja karman --<-----
Reeza
Super User

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 Smiley Happy.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 797 views
  • 2 likes
  • 5 in conversation