DATA Step, Macro, Functions and more

PUTC function to convert macro variable DATE field YYMMDD10. format

Reply
Frequent Contributor
Posts: 95

PUTC function to convert macro variable DATE field YYMMDD10. format

 

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

 

Trusted Advisor
Posts: 1,933

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

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??

 

 

Frequent Contributor
Posts: 95

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

Posted in reply to PaigeMiller

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

Trusted Advisor
Posts: 1,933

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

[ Edited ]

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
PROC Star
Posts: 326

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

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.

Super User
Super User
Posts: 7,078

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

[ Edited ]

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
  )
;

 

Trusted Advisor
Posts: 1,933

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

[ Edited ]

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.

Super User
Posts: 5,518

Re: PUTC function to convert macro variable DATE field YYMMDD10. format

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?

 

Ask a Question
Discussion stats
  • 7 replies
  • 244 views
  • 2 likes
  • 5 in conversation