topic Re: CALCULATED references in the immediate query in SAS Programming

CALCULATED references in the immediate query

Babloo — Wed, 28 Aug 2019 11:40:33 GMT

I would like to know how to use the variable which was created in proc SQL in the same step for join. Please guide me to resolve this issue. Intention of this step is to complete it in one step.

Log:

Re: CALCULATED references in the immediate query

PeterClemmensen — Tue, 27 Aug 2019 10:29:32 GMT

As it says in Problem Note 41911, you can get around this issue by moving the calculated reference to the beginning or to another location, so that it is not the last reference.

Re: CALCULATED references in the immediate query

Babloo — Wed, 28 Aug 2019 11:41:08 GMT

I moved the calculated reference variable to front as instructed but still getting the error.

Re: CALCULATED references in the immediate query

PeterClemmensen — Tue, 27 Aug 2019 10:46:06 GMT

Ok, got it. Check out this small example. The first PROC SQL gives an error. The second does not.

Should give you your answer 🙂

data one;
input ID var1;
datalines;
1 10
2 20
3 30
;

data two;
input ID $ var2;
datalines;
1 10
3 30
;

/* This gives error */
proc sql;
    create table three as
    select one.*, two.var2, cats(one.id) as charID
    from one left join two
    on calculated charID = two.ID;
quit;

/* This does not */
proc sql;
    create table three as
    select one.*, two.var2
    from one left join two
    on cats(one.id) = two.ID;
quit;

Either use the CALCULATED keyword in a Having Clause, or do the calculation directly in the On Clause.

Re: CALCULATED references in the immediate query

Jagadishkatam — Tue, 27 Aug 2019 10:45:03 GMT

I believe you should use the calculated with having in proc sql, so please try this below untested code

   proc sql;
         create table temp3 as
              select cats(substr(put(a.ISSUE_DT,yymmn6.),1,4),"-",substr(put(a.ISSUE_DT,yymmn6.),5)) as
ISSUE_MONTH_INS_CA_FL,a.*,b.INITIAL_DT from INSURANCE_CASHFLOW_updated2  as a left join ADP_OUTPUT_NON_LIFE
as b
                on a.REPORTING_DT=b.REPORTING_DT and
a.ENTITY_ID=b.UNIT
    having calculated ISSUE_MONTH_INS_CA_FL=b.ISSUE_MONTH and a.REPORTING_CAUSE="ACT";

quit;

Re: CALCULATED references in the immediate query

Tom — Tue, 27 Aug 2019 13:06:03 GMT

@Jagadishkatam has given you the answer.

You cannot use CALCULATED in an ON clause because that is where you are telling SAS how to combine the tables and generate the data records that are used to generate the columns listed between SELECT and FROM. So you cannot use the output to select the input.

You can either repeat the logic to derive the variable in the ON condition or use a HAVING condition to filter the rows after they have been generated.

Re: CALCULATED references in the immediate query

ballardw — Tue, 27 Aug 2019 14:41:22 GMT

FWIW , instead of a pretty ugly bit of code:

cats(substr(put(a.ISSUE_DT,yymmn6.),1,4),"-",substr(put(a.ISSUE_DT,yymmn6.),5))

Try

put(a.ISSUE_DT,yymmd7.)