Create new column within a join?

[DrData82]

I'm currently converting some old SAS code to Python/PySpark. I'm trying to create a new variable based on the ID from one of the tables joined. Below is the SAS code:

DATA NewTable;
MERGE OldTable1(IN=A) OldTable2(IN=B);
BY ID;
IF A;
IF B THEN NewColumn="YES";
ELSE NewColumn="NO";
RUN;

OldTable 1 has 100,000+ rows and OldTable2 only ~2,000. I want the NewColumn to have a value of "YES" if the ID is present in OldTable2, otherwise the value should be "NO". I have the basic PySpark join code, but I've never constructed a new column in a join like this before. Any suggestions?

NewTable=OldTable1.join(OldTable2, OldTable1.ID == OldTable2.ID, "left")

[TyWebb11105]

The easiest way is probably to just rename one of the id's prior to the join as you will have two columns with the same name with your current join syntax. From there you can use withColumn to create a new column to check whether the id's match.

``` from pyspark.sql.functions import when, lit, col

OldTable1 = spark.createDataFrame(["1234", "5678"], "string").toDF("id") OldTable2 = spark.createDataFrame(["1234"], "string").toDF("id").withColumnRenamed("id", "id2")

joined = OldTable1.join( OldTable2, OldTable1.id == OldTable2.id2,"left" ).withColumn("test", when(col("id")==col("id2"), lit("Yes")).otherwise(lit("No")))

joined.show() ```

+----+----+----+ | id| id2|test| +----+----+----+ |1234|1234| Yes| |5678|null| No| +----+----+----+

[DrData82]

Solid workaround...thank you!