topic I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node. in SAS Data Management

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

channa43 — Sun, 28 Jan 2018 22:52:58 GMT

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

In Di Studio (Version 4.901) I try this in an Extract node and gives an error "Summary Function restricted to Select clause" etc.

The expression is; catx("_",(min(intnx('Month',(Datepart(Policy_Period_Create_Time)),0,'E')) ),Account_Number)

However the same expression runs correctly in SAS EG.

Appreciate any help here.

Cheers

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

PGStats — Sun, 28 Jan 2018 23:21:35 GMT

The error message isn't about the expression itself. It is about where in the query you used it. Give more détails, please.

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

LinusH — Mon, 29 Jan 2018 12:28:02 GMT

Please show the generated code, and potentially screen shots on relevant transformation tabs.

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

channa43 — Tue, 30 Jan 2018 08:31:52 GMT

Hi PG

I am creating DIS reporting job, in the process I have to select the minimum effective date (month) group by account ids (and creating a unique Id with the effective month and Account ID).

So I created this in SAS EG and all worked fine, however when I re created this step in DIS the error was given and couldn't go beyond.

Attached a screenshots of the Extract Mapping hope this will help.

Thanks heaps

Channa

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

channa43 — Tue, 30 Jan 2018 08:51:46 GMT

The DIS Code is given below , I am creating an ID with minimum date and Account id, so unable to execute the minimum date expression in DIS in a extract transformation, however the code works in SAS EG.

Thanks heaps

Channa

proc sql;

create table work.Subsequent_Quotes_0 as

select

Account_ID,

(intnx('Month',(Datepart(_Period_Create_Time)),0,'E')) as

Period_Created_Month length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'Period Created Month',

(catx("_",(min(intnx('Month',(Datepart(Period_Create_Time)),0,'E')) ),Account_Number)) as Account_Policy_ID length = 8

label = 'Account Policy ID',

Period_Create_Time,

Job_Type

from &SYSLAST

group by

Account_ID,

;

quit;

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

LinusH — Tue, 30 Jan 2018 14:30:31 GMT

I need to see the full mapping tab, and the full generated code for that step.

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

channa43 — Wed, 31 Jan 2018 10:40:22 GMT

Hi Thanks for the responses, now this has been resolved

Cheers