### Set Up PySpark Environment in Google Colab

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Initializes the PySpark environment in Google Colab, including installing the pyspark library, creating a SparkSession, and loading data from CSV files stored in Google Drive. It also includes steps to verify data loading and schema.

```python
# Installing pyspark
!pip3 install pyspark

# Connecting spark
import pyspark
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

# Importing data from Google Drive (mount Drive first)
df_cars_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/cars_data.txt",
                                               inferSchema = True,
                                               header = True,
                                               delimiter = "|")
df_custom_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/customization_data.txt",
                                                 inferSchema = True,
                                                 header = True,
                                                 delimiter = "|")
df_gaming_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/gaming_data.txt",
                                                 inferSchema = True,
                                                 header = True,
                                                 delimiter = "|")

# Verify data loaded correctly
df_cars_data.show(5, False)
df_cars_data.printSchema()
```

--------------------------------

### Compute Summary Statistics in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

This example shows how to compute descriptive statistics for all columns in a PySpark DataFrame, including count, mean, standard deviation, minimum, maximum, and quartile values. This functionality mirrors SAS's PROC MEANS and PROC FREQ procedures.

```python
# PySpark implementation
df_result = df_cars_data.summary()
df_result.show(truncate=False)
```

--------------------------------

### Install and Initialize PySpark in Google Colab

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/README.md

This snippet demonstrates the necessary steps to install the PySpark library and initialize a SparkSession within a Google Colab environment. These are prerequisite steps before executing any PySpark code.

```python
# Installing pyspark
!pip3 install pyspark

# Connecting spark
import pyspark
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
```

--------------------------------

### PySpark: Order Data by Multiple Columns and Select Top N Rows

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-008.md

This PySpark code replicates SAS's data ordering and top N row selection. It uses select(), orderBy() with desc(), and limit() to achieve the same results as the SAS PROC SORT example, ordering by 'mpg_city' and 'mpg_highway' in descending order.

```python
import pyspark.sql.functions as fn

df_cars_mpg_city_by = df_cars_data.select(fn.col("code"),
                                          fn.col("make"),
                                          fn.col("model"),
                                          fn.col("mpg_city"),
                                          fn.col("mpg_highway")).orderBy(fn.col("mpg_city").desc(),fn.col("mpg_highway").desc())

df_cars_mpg_city_by = df_cars_mpg_city_by.limit(25)
df_cars_mpg_city_by.show(25, truncate = False)

cars_mpg_highway_key = df_cars_data.select(fn.col("code"),
                                           fn.col("make"),
                                           fn.col("model"),
                                           fn.col("mpg_city"),
                                           fn.col("mpg_highway")).orderBy(fn.col("mpg_highway").desc(),fn.col("mpg_city").desc())

cars_mpg_highway_key = cars_mpg_highway_key.limit(25)
cars_mpg_highway_key.show(25, truncate = False)
```

--------------------------------

### Group, Count, Order, and Limit Results in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

This snippet demonstrates how to group data by a specific column, count the occurrences in each group, order the results in descending order, and then limit the output to the top N records. It's the PySpark equivalent of using SAS's BY statement with PROC SORT and the OBS option.

```python
import pyspark.sql.functions as fn

# PySpark implementation
df_cars = df_cars_data.groupBy(fn.col("make")).count()
df_ordered = df_cars.withColumnRenamed("count", "total") \
                    .orderBy(fn.col("total").desc(), fn.col("make").asc())
df_top10 = df_ordered.limit(10)
df_top10.show(truncate=False)
```

--------------------------------

### PySpark: Get Dataset Metadata

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-007.md

This PySpark code snippet replicates the functionality of SAS PROC CONTENTS. It prints the schema of the DataFrame, then iterates through the DataFrame's data types and schema properties to construct a new DataFrame containing column names, data types, and nullability. Finally, it displays the resulting metadata.

```python
import pyspark.sql.functions as fn

# Just for showing metadata information
df_cars_data.printSchema();

# Get metadata information for use later
columns = ["name", "datatype", "nullable"]
values = [("-", "-", False)]
df_metadata = spark.createDataFrame(values, columns)

for column_data in df_cars_data.dtypes:
  newRow = spark.createDataFrame([(column_data[0], column_data[1], df_cars_data.schema[column_data[0]].nullable)], columns)
  df_metadata = df_metadata.union(newRow)

df_metadata = df_metadata.filter(fn.col("name") != "-")
df_metadata.show(truncate = False)
```

--------------------------------

### PySpark: Filter Rows and Select Columns

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-002.md

This PySpark code replicates the SAS functionality by filtering rows where the 'make' column equals 'Audi' and selecting the 'code', 'make', 'model', 'type', and 'msrp' columns. It leverages the `select()` and `filter()` transformations with `col()` for column referencing.

```python
import pyspark.sql.functions as fn

df_result = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("type"),
                                fn.col("msrp"))\
                        .filter(fn.col("make") == "Audi")

df_result.show(truncate=False)
```

--------------------------------

### Execute SQL Queries on PySpark DataFrames

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Runs SQL queries on PySpark DataFrames by registering them as temporary views. This is the PySpark equivalent of using PROC SQL in SAS. It allows users familiar with SQL to perform complex data selections and transformations.

```python
# PySpark implementation
df_cars_data.createOrReplaceTempView("cars")

sql_query = """select code,
                      make,
                      model,
                      type,
                      origin,
                      drivetrain,
                      msrp,
                      invoice,
                      horsepower,
                      weight
                 from cars
                where make = 'Lexus'
                  and type = 'Sedan'
                  and invoice > 40000"""

lexus_sedan_cars = spark.sql(sql_query)
lexus_sedan_cars.show(5, truncate=False)
```

--------------------------------

### SAS: Count rows by group, order, and get top 10

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-004.md

This SAS code counts the number of rows for each 'make', orders the results by the count in descending order, and then selects the top 10 rows. It utilizes data steps for counting and sorting, and a proc sort for ordering. The 'obs' option limits the output.

```sas
data cars (keep = make total);
    set datalib.cars_data;
    by make;

    if first.make then
        total = 0;
    total + 1;

    if last.make;
run;

proc sort data = cars out = cars_ordered;
    by descending total;
run;

data cars_top10;
    set cars_ordered (obs = 10);
run;
```

--------------------------------

### PySpark: Count rows by group, order, and get top 10

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-004.md

This PySpark code replicates the SAS functionality by grouping the DataFrame by the 'make' column, counting the occurrences in each group, renaming the count column to 'total', ordering the results by 'total' in descending order, and finally limiting the output to the top 10 rows. It uses functions from pyspark.sql.functions.

```python
import pyspark.sql.functions as fn

df_cars = df_cars_data.groupBy(fn.col("make")).count()
df_ordered = df_cars.withColumnRenamed("count","total").orderBy(fn.col("total").desc(),fn.col("make").asc())
df_top10 = df_ordered.limit(10)
df_top10.show(truncate = False)
```

--------------------------------

### Filter Data and Select Columns in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Shows how to filter rows based on a condition and select specific columns from a PySpark DataFrame using `select()`, `col()`, and `filter()`. This mirrors the functionality of SAS `KEEP` option with an `IF` statement.

```python
import pyspark.sql.functions as fn

# PySpark implementation
df_result = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("type"),
                                fn.col("msrp"))\
                        .filter(fn.col("make") == "Audi")

df_result.show(truncate=False)
```

--------------------------------

### Order Data with Multiple Columns in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

This snippet illustrates how to sort a PySpark DataFrame based on multiple columns, allowing for both ascending and descending order specifications. This is the PySpark equivalent of using SAS's PROC SORT with BY or KEY statements.

```python
import pyspark.sql.functions as fn

# PySpark implementation
df_cars_mpg_city_by = df_cars_data.select(fn.col("code"),
                                          fn.col("make"),
                                          fn.col("model"),
                                          fn.col("mpg_city"),
                                          fn.col("mpg_highway"))\
                                   .orderBy(fn.col("mpg_city").desc(),
                                           fn.col("mpg_highway").desc())

df_cars_mpg_city_by = df_cars_mpg_city_by.limit(25)
df_cars_mpg_city_by.show(25, truncate=False)
```

--------------------------------

### SAS: Filter Rows and Select Columns (KEEP Option)

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-002.md

This SAS code filters a dataset to include only rows where the 'make' is 'Audi' and selects specific columns (code, make, model, type, msrp) for the output dataset. It uses the `KEEP` option to specify the desired columns and an `IF` statement for row filtering.

```sas
data cars (keep = code make model type msrp);
    set datalib.cars_data;

    if make = 'Audi';
run;
```

--------------------------------

### SAS: Order Data by Multiple Columns and Select Top Rows

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-008.md

This SAS code snippet demonstrates ordering a dataset named 'cars' by 'mpg_city' and 'mpg_highway' in descending order using PROC SORT with both BY and KEY statements. It then selects the top 25 rows for each ordering method into new datasets.

```sas
data cars (keep = code make model mpg_city mpg_highway);
    set datalib.cars_data;
run;

proc sort data = cars out = cars_mpg_city_by;
    by descending mpg_city descending mpg_highway;
run;

proc sort data = cars out = cars_mpg_highway_key;
    key mpg_highway / descending;
    key mpg_city / descending;
run;

data cars_mpg_city_by;
    set cars_mpg_city_by (obs = 25);
run;

data cars_mpg_highway_key;
    set cars_mpg_highway_key (obs = 25);
run;
```

--------------------------------

### SAS: Get Dataset Metadata with PROC CONTENTS

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-007.md

This SAS code snippet retrieves metadata for a dataset named 'cars' using PROC CONTENTS and stores it in a new dataset 'cars_metadata'. It then sorts the metadata and transforms the 'type' column to represent data types as 'Numeric' or 'String'. This is useful for understanding dataset structure and column properties.

```sas
data cars;
    set datalib.cars_data;
run;

proc contents data = cars out = cars_metadata order = varnum;
    title 'The Contents of the CARS Data Set';
run;

proc sort data = cars_metadata;
    by varnum;
run;

data cars_metadata (keep = varnum name datatype length format formatl);
    set cars_metadata;
    length DataType $15;

    if type = 1 then
        do;
            DataType = "Numeric";
        end;
    else
        do;
            DataType = "String";
        end;
run;
```

--------------------------------

### Add New Columns with Literal Values in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Demonstrates how to add new columns to a PySpark DataFrame with constant literal values using the `withColumn()` and `lit()` functions. This is the PySpark equivalent of a SAS data step with column assignment.

```python
import pyspark.sql.functions as fn

# PySpark implementation
df_cars_data = df_cars_data.withColumn("car_category", fn.lit("Luxury"))\
                           .withColumn("engine_modifications", fn.lit("Stock"))

df_cars_data.limit(5).show(truncate=False)
```

--------------------------------

### PySpark: Get Statistics for All Columns (summary)

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-006.md

This PySpark code snippet calculates and displays summary statistics (count, mean, stddev, min, max, etc.) for all columns (numeric and non-numeric) in the 'df_cars_data' DataFrame using the `summary()` function. Null values are shown for statistics that do not apply to a particular column type.

```python
df_result = df_cars_data.summary()
df_result.show(truncate = False)
```

--------------------------------

### Join DataFrames with Filters and Count Rows in PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Performs an inner join between two PySpark DataFrames based on a common key ('code') and an additional filter condition on one of the DataFrames. It then counts the resulting rows, mimicking SAS MERGE with IN= and WHERE= options followed by a `PROC SQL` count. This operation is useful for combining related datasets and filtering based on specific criteria.

```python
import pyspark.sql.functions as fn

# PySpark implementation
cars_color_customized = df_cars_data.alias("ca")
    .join(df_custom_data.alias("cu"),
          (fn.col("ca.code") == fn.col("cu.code")) & (fn.col("cu.color_custom") == 1),
          "inner")

print(f"Number of Observations = {cars_color_customized.count()}")
```

--------------------------------

### Import Customization Data into SAS Library

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/import-to-sas.md

This SAS code snippet imports data from a tab-delimited text file named 'customization_data.txt' into a SAS dataset named 'custom_data'. It then prints the data and saves it to a library named 'datalib'. Replace '<your_sas_userid>' with your actual SAS User ID.

```sas
proc import file = "/home/<your_sas_userid>/sasuser.v94/customization_data.txt" out = custom_data dbms = tab replace;
    delimiter = "|";
    guessingrows = 500;
run;

proc print data = custom_data;
run;

data datalib.custom_data;
    set custom_data;
run;
```

--------------------------------

### Print PySpark DataFrame Schemas

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/importing-to-colab.md

This code snippet demonstrates how to print the schema of Spark DataFrames loaded from CSV files. The `printSchema()` method displays the column names, data types, and nullability for each column in the DataFrame. This is useful for verifying that data has been loaded correctly and understanding the structure of the data.

```python
df_cars_data.printSchema()
df_custom_data.printSchema()
df_gaming_data.printSchema()
```

--------------------------------

### SAS: Append Datasets Using PROC APPEND and Data Step SET

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-010.md

This SAS code demonstrates appending datasets. It first creates three separate datasets ('audi_cars', 'bmw_cars', 'mercedes_cars') by filtering a base dataset ('datalib.cars_data') based on car make and MSRP. Then, it appends 'audi_cars' and 'bmw_cars' into 'all_cars' using a data step SET option, followed by appending 'mercedes_cars' to 'all_cars' using PROC APPEND with the FORCE option.

```sas
data audi_cars (keep = code make model type origin msrp);
    set datalib.cars_data;

    if make = "Audi" and msrp > 50000;
run;

data bmw_cars (keep = code make model type origin msrp);
    set datalib.cars_data;

    if make = "BMW" and msrp > 50000;
run;

data mercedes_cars (keep = code make model type origin msrp);
    set datalib.cars_data;

    if make = "Mercedes-Benz" and msrp > 50000;
run;

data all_cars;
    set audi_cars bmw_cars;
run;

proc append base = all_cars data = mercedes_cars force;
run;
```

--------------------------------

### Import Gaming Data into SAS Library

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/import-to-sas.md

This SAS code snippet imports data from a tab-delimited text file named 'gaming_data.txt' into a SAS dataset named 'gaming_data'. It then prints the data and saves it to a library named 'datalib'. Remember to update '<your_sas_userid>' with your actual SAS User ID.

```sas
proc import file = "/home/<your_sas_userid>/sasuser.v94/gaming_data.txt" out = gaming_data dbms = tab replace;
    delimiter = "|";
    guessingrows = 500;
run;

proc print data = gaming_data;
run;

data datalib.gaming_data;
    set gaming_data;
run;
```

--------------------------------

### Categorize and Count Game Data with SELECT-WHEN in SAS

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-017.md

This SAS code calculates the total availability of a car across multiple video games, assigns a category based on the availability count using a SELECT-WHEN statement, and then sorts and counts the number of cars in each category. It creates a new dataset showing the category and its total count. Dependencies include the 'datalib.gaming_data' dataset.

```sas
data gaming;
    set datalib.gaming_data;
    total_availability = gt7_available + acc_available + fh5_available + pc2_available + ir_available + rf2_available;

    select;
        when (total_availability = 0) availability_category = "NOT AVAILABLE";
        when (total_availability = 1) availability_category = "SINGLE";
        when (2 <=total_availability <=5) availability_category = "MEDIUM";
        when (total_availability = 6) availability_category = "FULL";
        otherwise availability_category = "NO DATA";
    end;
run;

proc sort data = gaming;
    by availability_category;
run;

data gaming_total_by_cat (keep = availability_category total);
    set gaming;
    by availability_category;

    if first.availability_category then
        total = 1;
    else
        total + 1;

    if last.availability_category;
run;
```

--------------------------------

### Import Cars Data into SAS Library

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/import-to-sas.md

This SAS code snippet imports data from a tab-delimited text file named 'cars_data.txt' into a SAS dataset named 'cars_data'. It then prints the data and saves it to a library named 'datalib'. Ensure to replace '<your_sas_userid>' with your actual SAS User ID.

```sas
proc import file = "/home/<your_sas_userid>/sasuser.v94/cars_data.txt" out = cars_data dbms = tab replace;
    delimiter = "|";
    guessingrows = 500;
run;

proc print data = cars_data;
run;

data datalib.cars_data;
    set cars_data;
run;
```

--------------------------------

### Append Multiple DataFrames Vertically with PySpark

Source: https://context7.com/apalominor/sas-to-pyspark-code-examples/llms.txt

Combines multiple PySpark DataFrames vertically using the `union()` operation. This is analogous to using a SET statement with multiple datasets in SAS DATA step or PROC APPEND. It requires that all DataFrames have the same schema.

```python
import pyspark.sql.functions as fn

# PySpark implementation
audi_cars = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("type"),
                                fn.col("origin"),
                                fn.col("msrp"))
                        .filter((fn.col("make") == "Audi") & (fn.col("msrp") > 50000))

bmw_cars = df_cars_data.select(fn.col("code"),
                               fn.col("make"),
                               fn.col("model"),
                               fn.col("type"),
                               fn.col("origin"),
                               fn.col("msrp"))
                       .filter((fn.col("make") == "BMW") & (fn.col("msrp") > 50000))

mercedes_cars = df_cars_data.select(fn.col("code"),
                                    fn.col("make"),
                                    fn.col("model"),
                                    fn.col("type"),
                                    fn.col("origin"),
                                    fn.col("msrp"))
                            .filter((fn.col("make") == "Mercedes-Benz") & (fn.col("msrp") > 50000))

all_cars = audi_cars.union(bmw_cars).union(mercedes_cars)
all_cars.show(40, truncate=False)
```

--------------------------------

### Create SAS Dataset Using SQL

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-014.md

This SAS code snippet creates a dataset named 'lexus_sedan_cars' by selecting specific columns from a pre-existing 'cars' dataset. It filters the data to include only 'Lexus' cars of type 'Sedan' with an invoice price greater than $40,000. The `proc sql` step is used for this SQL-based data manipulation.

```sas
data cars;
    set datalib.cars_data;
run;

proc sql;
    create table lexus_sedan_cars as
    select code,
           make,
           model,
           type,
           origin,
           drivetrain,
           msrp,
           invoice,
           horsepower,
           weight
      from cars
     where make = 'Lexus'
       and type = 'Sedan'
       and invoice > 40000;
run;
```

--------------------------------

### PySpark: Filter, Join, and Transform Car and Gaming Data

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-016.md

This PySpark code filters car data for Porsches under 400 horsepower and gaming data for PC availability. It then joins these datasets and transforms binary availability flags into 'Yes'/'No' strings using `withColumn` and conditional logic. This replicates the SAS logic for preparing car data for analysis.

```python
# Assuming 'cars_df' and 'gaming_df' are already loaded PySpark DataFrames

# Filter Porsche cars with less than 400 horsepower
porsche_cars_df = cars_df.filter((col("make") == "Porsche") & (col("horsepower") < 400))

# Filter for PC gaming availability
# Define the condition for PC games availability
pc_gaming_condition = (
    (col("acc_available") == 1) | 
    (col("fh5_available") == 1) | 
    (col("pc2_available") == 1) | 
    (col("ir_available") == 1) | 
    (col("rf2_available") == 1)
)

# Apply the condition and ensure not available in GT7
only_pc_gaming_df = gaming_df.filter(pc_gaming_condition & (col("gt7_available") == 0))

# Join the filtered datasets
# Ensure both dataframes have the 'code' column for joining
porsche_only_pc_cars_df = porsche_cars_df.alias("pc").join(
    only_pc_gaming_df.alias("opg"),
    col("pc.code") == col("opg.code"),
    "inner" # Using inner join to replicate the 'if pc = opg' logic in SAS
).select(
    "pc.code",
    "pc.make",
    "pc.model",
    "pc.type",
    "pc.horsepower",
    "opg.acc_available",
    "opg.fh5_available",
    "opg.pc2_available",
    "opg.ir_available",
    "opg.rf2_available"
)

# Transform binary availability flags to 'Yes'/'No'
porsche_only_pc_cars_transformed_df = porsche_only_pc_cars_df.withColumn("acc_available_flag", 
    when(col("acc_available") == 1, "Yes").otherwise("No")
).withColumn("fh5_available_flag", 
    when(col("fh5_available") == 1, "Yes").otherwise("No")
).withColumn("pc2_available_flag", 
    when(col("pc2_available") == 1, "Yes").otherwise("No")
).withColumn("ir_available_flag", 
    when(col("ir_available") == 1, "Yes").otherwise("No")
).withColumn("rf2_available_flag", 
    when(col("rf2_available") == 1, "Yes").otherwise("No")
)

# Select the final columns as per SAS output (excluding original binary flags)
final_porsche_cars_df = porsche_only_pc_cars_transformed_df.select(
    "code", 
    "make", 
    "model", 
    "type", 
    "horsepower", 
    "acc_available_flag", 
    "fh5_available_flag", 
    "pc2_available_flag", 
    "ir_available_flag", 
    "rf2_available_flag"
)

final_porsche_cars_df.show()
```

--------------------------------

### Create PySpark Dataset Using SQL

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-014.md

This PySpark code snippet replicates the SAS functionality by creating a temporary view named 'cars' from a DataFrame `df_cars_data`. It then uses the `spark.sql()` method to execute a SQL query, filtering for 'Lexus' cars of type 'Sedan' with an invoice price over $40,000, and stores the result in the `lexus_sedan_cars` DataFrame. This approach demonstrates how to leverage SQL syntax within PySpark for data manipulation.

```python
df_cars_data.createOrReplaceTempView("cars")

sql_query = """select code,
                      make,
                      model,
                      type,
                      origin,
                      drivetrain,
                      msrp,
                      invoice,
                      horsepower,
                      weight
                 from cars
                where make = 'Lexus'
                  and type = 'Sedan'
                  and invoice > 40000"""

lexus_sedan_cars = spark.sql(sql_query)
lexus_sedan_cars.show(5, truncate = False)
```

--------------------------------

### SAS: Append Datasets with Different Columns

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-011.md

This SAS code demonstrates appending datasets with varying column definitions. It first creates three datasets (audi_cars, bmw_cars, mercedes_cars) with specific columns and filters. Then, it combines audi_cars and bmw_cars using a data step, followed by appending mercedes_cars using PROC APPEND with FORCE and NOWARN options to handle schema differences.

```sas
data audi_cars (keep = code make model type origin msrp);
    set datalib.cars_data;

    if make = "Audi" and msrp > 50000;
run;

data bmw_cars (keep = code make model origin msrp cylinders horsepower);
    set datalib.cars_data;

    if make = "BMW" and msrp > 50000;
run;

data mercedes_cars (keep = code make model msrp horsepower);
    set datalib.cars_data;

    if make = "Mercedes-Benz" and msrp > 50000;
run;

data all_cars;
    set audi_cars bmw_cars;
run;

proc append base = all_cars data = mercedes_cars force nowarn;
run;
```

--------------------------------

### Import CSV Data into PySpark DataFrames

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/importing-to-colab.md

This code snippet shows how to load data from multiple CSV files into separate Spark DataFrames. It assumes the CSV files are located in Google Drive and uses '|' as a delimiter. The `inferSchema` option automatically detects column data types, and `header=True` uses the first row as column names. The `show(5, False)` command displays the first 5 rows without truncating content.

```python
df_cars_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/cars_data.txt", inferSchema = True, header = True, delimiter = "|")
df_custom_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/customization_data.txt", inferSchema = True, header = True, delimiter = "|")
df_gaming_data = spark.read.format("csv").load("/content/drive/<path_to_your_drive>/gaming_data.txt", inferSchema = True, header = True, delimiter = "|")

df_cars_data.show(5, False)
df_custom_data.show(5, False)
df_gaming_data.show(5, False)
```

--------------------------------

### SAS: Filter, Join, and Transform Car and Gaming Data

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-016.md

This SAS code filters car data for Porsches under 400 horsepower and gaming data for PC availability. It then merges these datasets and transforms binary availability flags into 'Yes'/'No' strings. This process prepares data for analysis by selecting relevant columns and reformatting them.

```sas
data porsche_cars;
    set datalib.cars_data;
    
    if make = "Porsche" and horsepower < 400;
run;

data only_pc_gaming;
    set datalib.gaming_data;
    
    if (acc_available = 1 or fh5_available = 1 or pc2_available = 1 or ir_available = 1 or rf2_available = 1) and gt7_available = 0;
run;

proc sort data = porsche_cars;
    by code;
run;

proc sort data = only_pc_gaming;
    by code;
run;

data porsche_only_pc_cars (keep = code make model type horsepower acc_available fh5_available pc2_available ir_available rf2_available);
    merge porsche_cars (in = pc)
          only_pc_gaming (in = opg);
    by code;
    if pc = opg;
run;

data porsche_only_pc_cars (keep = code make model type horsepower acc_available_flag fh5_available_flag pc2_available_flag ir_available_flag rf2_available_flag);
    set porsche_only_pc_cars;

    if acc_available = 1 then acc_available_flag = "Yes";
    else acc_available_flag = "No";
    
    if fh5_available = 1 then fh5_available_flag = "Yes";
    else fh5_available_flag = "No";
    
    if pc2_available = 1 then pc2_available_flag = "Yes";
    else pc2_available_flag = "No";
    
    if ir_available = 1 then ir_available_flag = "Yes";
    else ir_available_flag = "No";
    
    if rf2_available = 1 then rf2_available_flag = "Yes";
    else rf2_available_flag = "No";
run;
```

--------------------------------

### Categorize and Count Game Data with PySpark

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-017.md

This PySpark code replicates the SAS functionality by calculating total availability, assigning categories using when() conditions, and then grouping by category to count occurrences. It renames the count column to 'total' and orders the results by category. Input is expected from a PySpark DataFrame named 'df_gaming_data'.

```python
import pyspark.sql.functions as fn

df_gaming = df_gaming_data.withColumn("total_availability", fn.col("gt7_available") + fn.col("acc_available") + fn.col("fh5_available") + fn.col("pc2_available") + fn.col("ir_available") + fn.col("rf2_available"))
df_gaming = df_gaming.withColumn("availability_category", fn.when(fn.col("total_availability") == 0, fn.lit("NOT AVAILABLE"))
                                                            .when(fn.col("total_availability") == 1, fn.lit("SINGLE"))
                                                            .when((fn.col("total_availability") >= 2) & (fn.col("total_availability") <= 5), fn.lit("MEDIUM"))
                                                            .when(fn.col("total_availability") == 6, fn.lit("FULL")),
                                                            .otherwise(fn.lit("NO DATA")))

df_gaming_total_by_cat = df_gaming.groupBy(fn.col("availability_category")).count()
df_gaming_total_by_cat = df_gaming_total_by_cat.withColumnRenamed("count", "total").orderBy(fn.col("availability_category").asc())

df_gaming_total_by_cat.show(5, False)
```

--------------------------------

### SAS: Filter Rows and Drop Columns

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-003.md

This SAS code filters a dataset to include only 'Kia' cars and then drops several specified columns from the resulting dataset. It uses a `data` step with `set` to read the data, an `if` statement for filtering, and the `drop` option to exclude columns.

```sas
data cars (drop = type origin drivetrain enginesize cylinders horsepower mpg_city mpg_highway weight wheelbase length);
    set datalib.cars_data;

    if make = 'Kia';
run;
```

--------------------------------

### PySpark: Filter Rows and Select Columns (Option 2)

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-003.md

This PySpark code filters a DataFrame to include only 'Kia' cars and then selects only the desired columns (`code`, `make`, `model`, `msrp`, `invoice`). It uses `select()` with `fn.col()` to specify columns and `filter()` for row selection. This method first selects columns, then filters rows.

```python
import pyspark.sql.functions as fn

df_result = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("msrp"),
                                fn.col("invoice"))\
                        .filter(fn.col("make") == "Kia")

df_result.show(truncate=False)
```

--------------------------------

### PySpark: Filter Rows and Drop Columns (Option 1)

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-003.md

This PySpark code filters a DataFrame to include only 'Kia' cars and then drops a list of specified columns. It uses `drop()` to remove columns and `filter()` with `fn.col()` for row selection. This method first removes columns, then filters rows.

```python
import pyspark.sql.functions as fn

df_result = df_cars_data.drop("type", "origin", "drivetrain", "enginesize", "cylinders", "horsepower", "mpg_city", "mpg_highway", "weight", "wheelbase", "length")\
                        .filter(fn.col("make") == "Kia")

df_result.show(truncate = False)
```

--------------------------------

### PySpark: Create Rownum by Group

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-013.md

This PySpark code replicates the SAS functionality by selecting specific columns, filtering for 'Europe' sports cars, and then using a window function to assign a row number within each 'make' partition, ordered by 'model'. It leverages `pyspark.sql.functions` and `pyspark.sql.window`.

```python
import pyspark.sql.functions as fn
from pyspark.sql.window import Window

cars = df_cars_data.select(fn.col("code"),
                           fn.col("make"),
                           fn.col("model"),
                           fn.col("type"),
                           fn.col("origin"))\                   .filter((fn.col("origin") == "Europe") & (fn.col("type") == "Sports"))

window_spec = Window.partitionBy("make").orderBy("model")
cars_rownum = cars.withColumn("rownum", fn.row_number().over(window_spec))

cars_rownum.show(25, truncate = False)
```

--------------------------------

### SAS: Join Datasets with Filter and Count Rows

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-015.md

This SAS code joins two datasets, 'cars' and 'custom', based on the 'code' column. It applies a filter to the 'custom' dataset where 'color_custom' equals 1. The resulting dataset 'cars_color_customized' is then used to count the number of observations, which is printed to the console. The code requires the 'datalib.cars_data' and 'datalib.custom_data' datasets to be available.

```sas
data cars;
    set datalib.cars_data;
run;

data custom;
    set datalib.custom_data;
run;

proc sort data = cars;
    by code;
run;

proc sort data = custom;
    by code;
run;

data cars_color_customized;
    merge cars (in = ca) 
          custom (in = cu where = (color_custom = 1));
    by code;
    if ca = cu;
run;

data _null_;
    if 0 then
        set cars_color_customized nobs = n;
    put "Number of Observations = " n;
    stop;
run;
```

--------------------------------

### PySpark: Append Datasets with Different Columns using unionByName

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-011.md

This PySpark code appends datasets with potentially different columns. It selects specific columns and filters data for Audi, BMW, and Mercedes-Benz cars separately. The datasets are then combined using unionByName with allowMissingColumns=True to handle discrepancies in column sets, ensuring all columns are included in the final DataFrame.

```python
import pyspark.sql.functions as fn

audi_cars = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("type"),
                                fn.col("origin"),
                                fn.col("msrp"))\
                        .filter((fn.col("make") == "Audi") & (fn.col("msrp") > 50000))

bmw_cars = df_cars_data.select(fn.col("code"),
                               fn.col("make"),
                               fn.col("model"),
                               fn.col("origin"),
                               fn.col("msrp"),
                               fn.col("cylinders"),
                               fn.col("horsepower"))\
                       .filter((fn.col("make") == "BMW") & (fn.col("msrp") > 50000))

mercedes_cars = df_cars_data.select(fn.col("code"),
                                    fn.col("make"),
                                    fn.col("model"),
                                    fn.col("msrp"),
                                    fn.col("horsepower"))\
                            .filter((fn.col("make") == "Mercedes-Benz") & (fn.col("msrp") > 50000))

all_cars = audi_cars.unionByName(bmw_cars, allowMissingColumns = True)
all_cars = all_cars.unionByName(mercedes_cars, allowMissingColumns = True)
all_cars.show(40, truncate = False)
```

--------------------------------

### PySpark: Append Datasets Using Select, Filter, and Union

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-010.md

This PySpark code replicates the SAS dataset appending logic. It selects specific columns and filters the 'df_cars_data' DataFrame to create 'audi_cars', 'bmw_cars', and 'mercedes_cars' DataFrames based on car make and MSRP. Subsequently, it unions these three DataFrames into a single 'all_cars' DataFrame.

```python
import pyspark.sql.functions as fn

audi_cars = df_cars_data.select(fn.col("code"),
                                fn.col("make"),
                                fn.col("model"),
                                fn.col("type"),
                                fn.col("origin"),
                                fn.col("msrp"))																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																								
```
    }
  ]
}
```
```

--------------------------------

### SAS: Add New Columns with Literal Values

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-001.md

This SAS code snippet shows two methods for adding new columns to a dataset. The first method explicitly defines the column length and then assigns a value, while the second assigns a value directly. The `obs=5` option limits the output to the first 5 rows.

```sas
data cars;
    set datalib.cars_data (obs = 5);
    
    * Option 1: Setting column name and length, then the value *;
    length car_category $10;
    car_category = 'Luxury';
    
    * Option 2: Setting column name with value directly *;
    engine_modifications = 'Stock';
run;
```

--------------------------------

### PySpark: Add New Columns with Literal Values

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-001.md

This PySpark code snippet demonstrates how to add two new columns, 'car_category' and 'engine_modifications', with constant string values 'Luxury' and 'Stock' respectively. It utilizes the `withColumn()` transformation and the `lit()` function for setting literal values. The `limit(5).show()` displays the first 5 rows of the modified DataFrame.

```python
import pyspark.sql.functions as fn

df_cars_data = df_cars_data.withColumn("car_category",fn.lit("Luxury"))\
                           .withColumn("engine_modifications",fn.lit("Stock"))

df_cars_data.limit(5).show(truncate=False)
```

--------------------------------

### PySpark: Join Datasets with Filter and Count Rows

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-015.md

This PySpark code replicates the SAS dataset join functionality. It joins two dataframes, aliased as 'ca' (cars_data) and 'cu' (custom_data), using an inner join. The join condition includes matching 'code' columns and filtering 'cu' where 'color_custom' is 1. Finally, it counts the rows in the resulting dataframe and prints the count.

```python
import pyspark.sql.functions as fn

cars_color_customized = df_cars_data.alias("ca").join(df_custom_data.alias("cu"), (fn.col("ca.code") == fn.col("cu.code")) & (fn.col("cu.color_custom") == 1), "inner")
print(f"Number of Observations = {cars_color_customized.count()}")
```

--------------------------------

### SAS: Remove Duplicates with PROC SORT NODUPKEY

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-009.md

This SAS code snippet demonstrates how to remove duplicate rows from a dataset using the `PROC SORT` with the `NODUPKEY` option. It also shows how to capture duplicate rows using the `dupout` option. The input dataset is `datalib.cars_data`, and the output is `cars_distinct` (unique rows) and `cars_duplicates_rep` (duplicate rows).

```sas
data cars (keep = make origin);
    set datalib.cars_data;
run;

proc sort data = cars nodupkey out = cars_distinct dupout = cars_duplicates_rep;
    by origin make;
run;
```

--------------------------------

### Filter and Join DataFrames in PySpark

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-016.md

This snippet demonstrates filtering two PySpark DataFrames based on specific conditions and then performing an inner join on them using a common 'code' column. It also includes adding new flag columns with 'Yes'/'No' values based on existing integer columns and finally selecting a subset of columns for display. Requires PySpark SQL functions.

```python
import pyspark.sql.functions as fn

df_porsche_cars = df_cars_data.select("*")\
                              .filter((fn.col("make") == "Porsche") & (fn.col("horsepower") < 400))

df_only_pc_gaming = df_gaming_data.select("*")\
                                  .filter(((fn.col("acc_available") == 1) | (fn.col("fh5_available") == 1) | (fn.col("pc2_available") == 1) | (fn.col("ir_available") == 1) | (fn.col("rf2_available") == 1)) & (fn.col("gt7_available") == 0))

porsche_only_pc_cars = df_porsche_cars.alias("pc").join(df_only_pc_gaming.alias("opg"), fn.col("pc.code") == fn.col("opg.code"), "inner")
porsche_only_pc_cars = porsche_only_pc_cars.withColumn("acc_available_flag", fn.when(fn.col("acc_available") == 1, fn.lit("Yes")).otherwise(fn.lit("No")))
                                           .withColumn("fh5_available_flag", fn.when(fn.col("fh5_available") == 1, fn.lit("Yes")).otherwise(fn.lit("No")))
                                           .withColumn("pc2_available_flag", fn.when(fn.col("pc2_available") == 1, fn.lit("Yes")).otherwise(fn.lit("No")))
                                           .withColumn("ir_available_flag", fn.when(fn.col("ir_available") == 1, fn.lit("Yes")).otherwise(fn.lit("No")))
                                           .withColumn("rf2_available_flag", fn.when(fn.col("rf2_available") == 1, fn.lit("Yes")).otherwise(fn.lit("No")))

porsche_only_pc_cars = porsche_only_pc_cars.select(fn.col("pc.code"),
                                                   fn.col("make"),
                                                   fn.col("model"),
                                                   fn.col("type"),
                                                   fn.col("horsepower"),
                                                   fn.col("acc_available_flag"),
                                                   fn.col("fh5_available_flag"),
                                                   fn.col("pc2_available_flag"),
                                                   fn.col("ir_available_flag"),
                                                   fn.col("rf2_available_flag"))

porsche_only_pc_cars.show(5, False)
```

--------------------------------

### PySpark: Remove Duplicates using dropDuplicates()

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-009.md

This PySpark code snippet shows an alternative way to remove duplicate rows by using the `dropDuplicates()` transformation on specified columns. It selects 'make' and 'origin' columns and then removes duplicates based on these columns. Finally, it orders the resulting DataFrame by 'origin' and 'make' in ascending order.

```python
import pyspark.sql.functions as fn

df_result = df_cars_data.select(fn.col("make"),
                                fn.col("origin"))
df_result = df_result.dropDuplicates(["make","origin"])
df_result = df_result.orderBy(fn.col("origin").asc(),fn.col("make").asc())

df_result.show(40, truncate = False)
```

--------------------------------

### PySpark: Create Rownum for Europe Sports Cars

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-012.md

This PySpark code replicates the SAS logic by filtering a DataFrame for 'Europe' origin 'Sports' cars and then adding a 'rownum' column using a window function. It requires importing `pyspark.sql.functions` and `pyspark.sql.window`.

```python
import pyspark.sql.functions as fn
from pyspark.sql.window import Window

cars = df_cars_data.select(fn.col("code"),
                           fn.col("make"),
                           fn.col("model"),
                           fn.col("type"),
                           fn.col("origin"))\
                   .filter((fn.col("origin") == "Europe") & (fn.col("type") == "Sports"))

window_spec = Window.orderBy(fn.lit("1"))
cars_rownum = cars.withColumn("rownum", fn.row_number().over(window_spec))

cars_rownum.show(25, truncate = False)
```

--------------------------------

### SAS: Create Rownum by Group

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-013.md

This SAS code first filters a dataset to include only 'Europe' sports cars and then adds a 'rownum' column that increments for each row within a 'make' group. It uses the `first.` variable to reset the row number for each new group.

```sas
data cars (keep = code make model type origin);
    set datalib.cars_data;

    if origin = 'Europe' and type = 'Sports';
run;

data cars_rownum_group;
    set cars;
    by make;
    rownum + 1;

    if first.make then
        rownum = 1;
run;
```

--------------------------------

### PySpark: Remove Duplicates using distinct()

Source: https://github.com/apalominor/sas-to-pyspark-code-examples/blob/main/contents/case-009.md

This PySpark code snippet replicates the SAS functionality of removing duplicates by selecting specific columns and then applying the `distinct()` transformation. It orders the resulting DataFrame by 'origin' and 'make' in ascending order. This method removes duplicates based on all selected columns.

```python
import pyspark.sql.functions as fn

df_result = df_cars_data.select(fn.col("make"),
                                fn.col("origin"))
df_result = df_result.distinct()
df_result = df_result.orderBy(fn.col("origin").asc(),fn.col("make").asc())

df_result.show(40, truncate = False)
```