SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!


1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

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

View solution in original post

5 REPLIES 5
SteveNZ
Obsidian | Level 7

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 ;

RichardinOz
Quartz | Level 8

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

RichardinOz
Quartz | Level 8

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

jcis7
Pyrite | Level 9

Thank you very much !

DBailey
Lapis Lazuli | Level 10

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1048 views
  • 3 likes
  • 4 in conversation