Hello,
I've two tables.
TableA has all observations.
TableB has 30 observations, all of which are in TableA.
They can be matched by an 'ID'
How do I create a new variable in TableA indicating that all the observations in TableB are 'late' (ie., new variable name = late; all IDs found in TableB are coded
with late=1 in TableA).
I know a long way: use a data step, and use). Is there a way to do using subquery in SQL?
data new;
set tableA;
late=0;
if ID in ('0223', '0123', '0124' etc) then late=1;
run;
Thanks!
Assuming both datasets are ordered by ID, the data step version of what i think you want is
Data want ;
Merge A B (Keep = ID In = InB) ;
By ID ;
late = InB ;
Run ;
Richard
Many ways to do it but easy one is:
data tableb ;
set tableb ;
late = 'late' ;
run ;
proc sql ;
create table new as
select A.*,B.id, B.late
from tableA as A left join tableB as B
on A.id = B.id ;
quit ;
One pass for SQL, which is better if the data is not sorted:
Proc SQL ;
Create Table want as
Select A.*
, Case
When B.ID IS NOT NULL
Then 1
Else 0
End
As late
From A
Left join B
On A.ID = B.ID
Order by A.ID
;
(Untested code)
Richard
Assuming both datasets are ordered by ID, the data step version of what i think you want is
Data want ;
Merge A B (Keep = ID In = InB) ;
By ID ;
late = InB ;
Run ;
Richard
Thank you very much !
without sorting or recoding...
proc sql;
select
t1.*,
case when t2.id is not null then 1 else 0 end as Late
from
tablea t1
left outer join tableb t2
on t1.id=t2.id;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: