topic Re: PUTC function to convert macro variable DATE field YYMMDD10. format in SAS Programming

PUTC function to convert macro variable DATE field YYMMDD10. format

SASAna — Tue, 16 May 2017 13:10:59 GMT

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

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

PaigeMiller — Tue, 16 May 2017 13:15:26 GMT

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

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

kiranv_ — Tue, 16 May 2017 13:17:29 GMT

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.

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

SASAna — Tue, 16 May 2017 13:31:30 GMT

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

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

Tom — Tue, 16 May 2017 13:42:29 GMT

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

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

Astounding — Tue, 16 May 2017 13:33:45 GMT

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?

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

PaigeMiller — Tue, 16 May 2017 13:46:02 GMT

@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

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

PaigeMiller — Tue, 16 May 2017 13:51:08 GMT

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.