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

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

hyo_tree — Wed, 15 Jul 2020 09:54:42 GMT

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;

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

Jagadishkatam — Wed, 15 Jul 2020 10:41:55 GMT

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;

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

Kurt_Bremser — Wed, 15 Jul 2020 10:56:08 GMT

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;

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

hyo_tree — Thu, 16 Jul 2020 02:46:35 GMT

Thanks for your clarification

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

hyo_tree — Thu, 16 Jul 2020 02:48:01 GMT

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