proc sql: How to use the newly generated variable in this step

[hyo_tree]

i want to utilize the proc sql to compute the value. the studyid need to be replaced by 'cats(study,id)'.

 usubjid = cats(the newly studyid,'-','subjid')

but the proc always use the The original  studyid in dm_raw. 

the data dm_raw:

ID	studyid	subjid	study
1	250	001	20200515
2	250	002	20200515
3	250	003	20200515
4	250	004	20200515

the procedure:

proc sql；

  create table dm as 

    select cats(study,id) as studyid,

               cats(studyid,'-',subjid) as usubjid

    from dm_raw;

[Jagadishkatam]

please try below code

proc sql；
  create table dm as 
    select cats(study,id) as studyid,
               cats(cats(study,id),'-',subjid) as usubjid
    from dm_raw;
quit;

[hyo_tree]

thank you Jagadishkatam. i think the option 'caculated' is the better choice.,

[Kurt_Bremser · Accepted solution]

Please do ALWAYS post your data in a data step with datalines, so we can be sure of variable types and other attributes, and the real contents of your dataset.

In this case, "study" looks like a date, so it may be that the CATS() function does not yield the expected result.

Also, ALWAYS use the proper subwindow for posting code ("little running man" right next to the one indicated, which is for logs):

Your code as posted contained a "funny" semicolon, causing a syntax ERROR.

If you need to use a newly created variable in further operations, use the CALCULATED keyword:

proc sql;
create table dm as 
  select
    cats(study,id) as studyid,
    cats(calculated studyid,'-',subjid) as usubjid
  from dm_raw
;
quit;

[hyo_tree]

Thanks for your clarification