Hi, I can not translate by converting to subqueries, it's confused. There is no way to do row_number() over (partition by ) in SAS Studio?
... View more
proc sql;
create table a as
select rank,
pos_id,
c_estado,
d_estado,
c_cartao_sup,
c_ndc_sup,
c_orgao_resp
from (select row_number() over (partition by pos_id order by c_estado asc, c_cartao_sup desc) as rank,
pos_id,
c_estado,
d_estado,
c_cartao_sup,
c_ndc_sup,
c_orgao_resp
from f_agreg_mens_pos)x
where rank=1;
run;
Editor's Note: The title was changed to more generally reflect the goal.
... View more
/*I want write a general expression for a code with this characteristics bellow:
9 NUMBERS NUMBERS FROM (0-9), 2 LETTES FROM (A-Z) AND 3 NUMBERS FROM (0-9), e.g: 000028393OE123
The code below is not working for the expression that i did. c_bi: SSSSSSSSSSA030 - WRONG R020782/492008 - WRONG R010114/970308 - WRONG R006767/653308 - WRONG 000164063BE033 - CORRECT 000264063LA021 - CORRECT */
proc sql;
create table a as
select c_bi,
prxmatch('/^[0-9]{9}[a-zA-Z]{2}[0-9]{3}',c_bi) as c_bi_dum,
from pdc.pdc_cli_bancarizacao
where length(c_bi)=14 and
c_nif='' and
c_passaporte='' and
cartao_residencia='' and
cedula_pessoal='' and
outro_documento='' and
numero_documento=''
order by c_bi desc;
run;
... View more
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 😞
... View more
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;
... View more