CALCULATED references in the immediate query

[Babloo]

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:

[PeterClemmensen]

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.

[Babloo]

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

[PeterClemmensen]

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. 

[Jagadishkatam]

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;

[Tom]

@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.

[ballardw]

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