BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

Hi SAS users,

 

Need help with solving this macro in PROC SQL.

 

PROC SQL;
CONNECT TO ORACLE(PATH=XXXX USER=YYYY. ORAPW=ZZZ);
execute (delete from A.&MyTable
where substr(RUN_DT,1,7) = &ANA_RUN_DT    (RUN_DT is a charcter field in table)
) by oracle;
disconnect from oracle;
QUIT;

 

 

ANA_RUN_DT is  2016-11

 

Error  -

 

ERROR: ORACLE execute error: ORA-01722: invalid number.

 

I tried putting "&ANA_RUN_DT" and  '&ANA_RUN_DT' , but it did not work.

 

Thanks,

Ana

3 REPLIES 3
Shmuel
Garnet | Level 18

I have little experience with oracle sql, anyhow

oracle date is a numeric variable while 2016-11 is a character variable.

You should ask oracle experts how to write this code line.

Shmuel
Garnet | Level 18
while using macro variable use double quotes as
"&ana_run" to resolve the macro
Tom
Super User Tom
Super User

I suspect that ORACLE wants you to use single quotes, but SAS will not expand the macro variable reference inside of single quotes.

Try this instead.

%unquote(%bquote('&ana_run_dt'))

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1382 views
  • 0 likes
  • 3 in conversation