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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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