I have the following Proc SQL:
proc sql; create table dm1 as select STUDYID, DOMAIN, dm.SubjectID, USUBJID
from train.dm,train.cp where dm.subjectid=cp.subjectid order by subjectid ; quit;
USUBJID should be the a concatination of SubjectID and STUDYID: USUBJID = STUDYID || SubjectID . How can it be actualized inside the Proc SQL? Thank you!
proc sql; create table dm1 as select STUDYID, DOMAIN, dm.SubjectID, SubjectID||STUDYID as USUBJID
from train.dm,train.cp where dm.subjectid=cp.subjectid order by subjectid ; quit;
proc sql; create table dm1 as select STUDYID, DOMAIN, dm.SubjectID, SubjectID||STUDYID as USUBJID
from train.dm,train.cp where dm.subjectid=cp.subjectid order by subjectid ; quit;
If any of your your SubjectID are shorter than the defined length of the variable you may get unwanted spaces in the middle of the USUBJID variable:
data junk; length subjectid $ 10; subjectid = 'abc'; studyid = '123'; run; proc sql; select studyid, subjectid,SubjectID||STUDYID as USUBJID from junk; run;
If you don't want the space in the middle then use Cats(SubjectId,StudyId) as Usubjid
CATS/CATT functions can be used similarly to any other function within PROC SQL.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.