BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

 

Hi SAS USers,

 

I needed some help in the below code  where i am using DATE format field (&nqt_fos, &nqt_los)  to convert to YYYYMMDD10. format . I am getting missing expression error.

 

 

proc sql;
connect to oracle(user=&myid orapw=&mypwd path="&mydb");
create table test as select * from connection to oracle
(select distinct
case_id,
%sysfunc(PUTC(a.&nqt_fos, YYMMDD10.)) as nqt_fos, 
%sysfunc(PUTC(a.&nqt_los, YYMMDD10.)) as nqt_los,

paid_amt

);
disconnect from oracle;
quit;

 

 

Thanks,

Ana

 

7 REPLIES 7
PaigeMiller
Diamond | Level 26

Have you tried using PUTN instead of PUTC?

 

Can we see more of the code?

 

Can we see the SASLOG?

 

Do macro variables &nqt_fos and &nqt_los have a value? What is their value?

 

Why do use the a. in a.&nqt_fox and a.&nqt_los??

 

 

--
Paige Miller
SASAna
Quartz | Level 8

Hi Paige,

 

Below is the example and rest of the code would be -

 

dates are being passed to a macro variables nqt_fos & nqt_los.

 

%LET nqt_fos = date1 ;   (   Ex :  12/5/2014 12:00:00AM)

%LET nqt_los = date2 ;    (Ex:  15/6/2014 12:00:00AM)

 

PROC SQL is called like the above code to change them into charcter YYMMDD10. format.

 

Thanks,

Ana

PaigeMiller
Diamond | Level 26

@SASAna wrote:

Hi Paige,

 

Below is the example and rest of the code would be -

 

dates are being passed to a macro variables nqt_fos & nqt_los.

 

%LET nqt_fos = date1 ;   (   Ex :  12/5/2014 12:00:00AM)

%LET nqt_los = date2 ;    (Ex:  15/6/2014 12:00:00AM)

 

PROC SQL is called like the above code to change them into charcter YYMMDD10. format.

 

Thanks,

Ana


In this case, you could use the ANYDTDTM informat (not format), you don't need the "a." in front of your variable names, %SYSFUNC is completely unnecessary, something like (untested code)

 

input(&nqt_fos,anydtdtm.) as nqt_fos
--
Paige Miller
kiranv_
Rhodochrosite | Level 12

Create your required macrovariable in call symput in data _null_ step and then use it in explicit pass through. SAS functions and Macro functions in connect statement i.e. in explicit pass through.

Tom
Super User Tom
Super User

What do your macro variables contain?

To use with %SYSFUNC(PUTN()) they should contain text version of date, either an actual number or a date literal.

But you are adding an alias prefix to them in your SQL as if you assumed they were going to be variable NAMES.

Let's assume that what you want to do is change the names of the variables you are pulling from the external database.

create table test as
select * from connection to oracle
  (select distinct
     case_id
   , &nqt_fos as nqt_fos
   , &nqt_los as nqt_los
   , paid_amt
  )
;

You could apply ORACLE functions to the variables in the pass through code.  But if you want to apply SAS function to them you will need to do that on the SAS side of the query.

create table test as
select
    case_id
  , put(datepart(nqt_fos),YYMMDDN10.) as char_nqt_fos
  , put(datepart(nqt_los),YYMMDDN10.) as char_nqt_los
  , paid_amt
 from connection to oracle
  (select distinct
     case_id
   , &nqt_fos as nqt_fos
   , &nqt_los as nqt_los
   , paid_amt
  from some_table
  )
;

 

PaigeMiller
Diamond | Level 26

So sayeth @Tom:

You could apply ORACLE functions to the variables in the pass through code.  But if you want to apply SAS function to them you will need to do that on the SAS side of the query.

 

Excellent point.

--
Paige Miller
Astounding
PROC Star

Many basic concepts need to be addressed ...

 

Why should %SYSFUNC be part of the solution?

 

Why are you referring to an alias named "a" when no such alias has been defined in the SQL code?

 

Why does your SQL code fail to mention the name of the source table?

 

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
  • 7 replies
  • 3314 views
  • 2 likes
  • 5 in conversation