SAS Studio: How to convert Convert Char(13) to date

[Romeo1]

Hi everyone,

could you kinldy help to convert Char(13) to date. The data char(13) is '2019-12-31 00' and I want to convert it do date. Another thing is that I want to select all information from one table and indicate that I only want 31 Dec 2019. This convertion i will use in the qery bellow.

proc sql;
create table teste_dmk as select dt_referencia,
gbncli                            as c_ndc,
gbncli_activo                 as c_ind_activo
from trs_mkt.trs_gbcli_activo_inactivo

where dt_referencia=31122019; HERE I NEED HELP.
quit;

[ed_sas_member]

Hi @Romeo1 

For the conversion into date, you can use the input() function with the proper informat:

data have;
	format want date9.;
	have = '2019-12-31 00';
	want = input(substr(have,1,10), YYMMDD10.);
run;

For the query, you can specify "31DEC2019"d

proc sql;
create table teste_dmk as select dt_referencia,
gbncli                            as c_ndc,
gbncli_activo                 as c_ind_activo
from trs_mkt.trs_gbcli_activo_inactivo
where dt_referencia="31DEC2019"d;
quit;

[Jagadishkatam · Accepted solution]

you can try below code

proc sql;
create table teste_dmk as select input(scan(dt_referencia,1,' '),yymmdd10.) as dt_referencia2,
gbncli                            as c_ndc,
gbncli_activo                 as c_ind_activo
from trs_mkt.trs_gbcli_activo_inactivo
having dt_referencia2='31dec2019'd; 
quit;

[Romeo1]

Thanks for your advise. I did it but is taking to long because the table as more than 12 gigs... 12 million of rowns...

Actually, I want to check how many duplicated values are in the table for one specifique date. for example:

PROC SQL;
SELECT CLNCLI AS C_NDC, COUNT(*) AS NR_REGISTOS
FROM TRS_MKT.TRS_GBCLI_ACTIVO_INACTIVO
WHERE INPUT(SCAN(DT_REFERENCIA,1,''),yymmdd10.)='31dec2019'd /* Here I want to convert the date because the date in on forma '2019-12-31 00'*/
GROUP BY CLNCLI ORDER BY 2 DESC;
QUIT;
It is not working 😞

[Tom]

What is actually in the data? 

First are you sure it is character and not a date (or datetime) value that is just being displayed using that year month day ordered string?

Why are you using the SCAN function?  If you specify a width in the informat specification then only the first 10 characters will be used.

INPUT(DT_REFERENCIA,yymmdd10.)='31dec2019'd 

And perhaps most important if you just want to the one value then why convert it to an actual date at all?

DT_REFERENCIA like '2019-12-31%'

Is the libref TRS_MKT pointing at some external database system? Perhaps SAS is unable to convert your code to run in the database and is pulling the whole database across ot SAS and then summarizing. Try writing an explicit passthru query.  Note that means you need to use the remote database's version of SQL.

PROC SQL;
connect using TRS_MKT;
select * from connection to TRS_MKT
(SELECT CLNCLI AS C_NDC, COUNT(*) AS NR_REGISTOS
  FROM TRS_GBCLI_ACTIVO_INACTIVO
  where DT_REFERENCIA like '2019-12-31%'
  group by CLNCLI 
)
ORDER BY 2 DESC
;
QUIT;