BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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:

 

 
6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

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
Rhodochrosite | Level 12

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

 

 
PeterClemmensen
Tourmaline | Level 20

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
Amethyst | Level 16

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;

 

 

Thanks,
Jag
Tom
Super User Tom
Super User

@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
Super User

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2231 views
  • 3 likes
  • 5 in conversation