187.EXPLORING A CUSTOM MONEY DATASET IN SAS | DATA CLEANING USING DATA STEP | STATUS ANALYSIS WITH PROC FREQ | VIEWING RAW DATA USING PROC PRINT | AGGREGATING TRANSACTIONS BY COUNTRY AND CURRENCY USING PROC SQL | TROUBLESHOOTING NO ROWS SELECTED IN PROC SQL | CASE HANDLING AND DATA DEBUGGING IN REAL-WORLD SAS PROJECTS

EXPLORING A CUSTOM MONEY DATASET IN SAS | DATA CLEANING USING DATA STEP | STATUS ANALYSIS WITH PROC FREQ | VIEWING RAW DATA USING PROC PRINT | AGGREGATING TRANSACTIONS BY COUNTRY AND CURRENCY USING PROC SQL | TROUBLESHOOTING NO ROWS SELECTED IN PROC SQL | CASE HANDLING AND DATA DEBUGGING IN REAL-WORLD SAS PROJECTS

/*Creating a detailed example of a MONEY dataset in SAS that includes various types of money transactions—like cash, credit, digital wallets, and bank transfers*/

Step 1: Creating a MONEY Dataset

data money;

    input Transaction_ID $ Customer_ID $ Transaction_Type:$15. Amount Currency $ 

          Transaction_Date : date9. Status: $15. Country $;

    format Transaction_Date date9.;    

    datalines;

T001 C001 Cash 150.75 USD 22MAY2025 Completed USA

T002 C002 Credit 250.00 USD 15MAY2025 Completed USA

T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA

T004 C001 BankTransfer 500.00 EUR 05MAY2025 Completed France

T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK

T006 C005 Credit 120.00 INR 17MAY2025 Completed India

T007 C006 DigitalWallet 75.25 USD 20MAY2025 Completed USA

T008 C007 BankTransfer 1000.00 USD 18MAY2025 Completed USA

T009 C008 Cash 60.00 EUR 12MAY2025 Completed France

T010 C002 Credit 200.00 USD 21MAY2025 Pending USA

T011 C009 DigitalWallet 45.00 GBP 22MAY2025 Completed UK

T012 C010 BankTransfer 700.00 INR 23MAY2025 Completed India

T013 C011 Cash 80.00 USD 25MAY2025 Completed USA

T014 C012 Credit 300.00 USD 27MAY2025 Failed USA

T015 C001 DigitalWallet 120.00 USD 28MAY2025 Completed USA

;

run;

proc print;run;

Output:

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country
1 T001 C001 Cash 150.75 USD 22MAY2025 Completed USA
2 T002 C002 Credit 250.00 USD 15MAY2025 Completed USA
3 T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA
4 T004 C001 BankTransfer 500.00 EUR 05MAY2025 Completed France
5 T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK
6 T006 C005 Credit 120.00 INR 17MAY2025 Completed India
7 T007 C006 DigitalWallet 75.25 USD 20MAY2025 Completed USA
8 T008 C007 BankTransfer 1000.00 USD 18MAY2025 Completed USA
9 T009 C008 Cash 60.00 EUR 12MAY2025 Completed France
10 T010 C002 Credit 200.00 USD 21MAY2025 Pending USA
11 T011 C009 DigitalWallet 45.00 GBP 22MAY2025 Completed UK
12 T012 C010 BankTransfer 700.00 INR 23MAY2025 Completed India
13 T013 C011 Cash 80.00 USD 25MAY2025 Completed USA
14 T014 C012 Credit 300.00 USD 27MAY2025 Failed USA
15 T015 C001 DigitalWallet 120.00 USD 28MAY2025 Completed USA


Step 2: Exploring the Dataset Using PROC PRINT and PROC CONTENTS

proc contents data=money; 

run;

Output:

                                                 The CONTENTS Procedure

Data Set Name WORK.MONEY Observations 15
Member Type DATA Variables 8
Engine V9 Indexes 0
Created 14/09/2015 00:03:42 Observation Length 72
Last Modified 14/09/2015 00:03:42 Deleted Observations 0
Protection   Compressed NO
Data Set Type   Sorted NO
Label      
Data Representation WINDOWS_64    
Encoding wlatin1 Western (Windows)    


Engine/Host Dependent Information
Data Set Page Size 65536
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 908
Obs in First Data Page 15
Number of Data Set Repairs 0
ExtendObsCounter YES
Filename C:\Users\Lenovo\AppData\Local\Temp\SAS Temporary Files\_TD14588_DESKTOP-QFAA4KV_\money.sas7bdat
Release Created 9.0401M2
Host Created X64_8HOME


Alphabetic List of Variables and Attributes
# Variable Type Len Format
4 Amount Num 8  
8 Country Char 8  
5 Currency Char 8  
2 Customer_ID Char 8  
7 Status Char 8  
6 Transaction_Date Num 8 DATE9.
1 Transaction_ID Char 8  
3 Transaction_Type Char 15  


proc print data=money(obs=10);

    title "First 10 Transactions in MONEY Dataset";

run;

Output:

First 10 Transactions in MONEY Dataset

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country
1 T001 C001 Cash 150.75 USD 22MAY2025 Complete USA
2 T002 C002 Credit 250.00 USD 15MAY2025 Complete USA
3 T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA
4 T004 C001 BankTransfer 500.00 EUR 05MAY2025 Complete France
5 T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK
6 T006 C005 Credit 120.00 INR 17MAY2025 Complete India
7 T007 C006 DigitalWallet 75.25 USD 20MAY2025 Complete USA
8 T008 C007 BankTransfer 1000.00 USD 18MAY2025 Complete USA
9 T009 C008 Cash 60.00 EUR 12MAY2025 Complete France
10 T010 C002 Credit 200.00 USD 21MAY2025 Pending USA


Step 3: Sorting Data by Transaction Date

proc sort data=money out=money_sorted;

    by Transaction_Date;

run;

proc print data=money_sorted(obs=10);

    title "Transactions Sorted by Date";

run;

Output:

Transactions Sorted by Date

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country
1 T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA
2 T004 C001 BankTransfer 500.00 EUR 05MAY2025 Complete France
3 T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK
4 T009 C008 Cash 60.00 EUR 12MAY2025 Complete France
5 T002 C002 Credit 250.00 USD 15MAY2025 Complete USA
6 T006 C005 Credit 120.00 INR 17MAY2025 Complete India
7 T008 C007 BankTransfer 1000.00 USD 18MAY2025 Complete USA
8 T007 C006 DigitalWallet 75.25 USD 20MAY2025 Complete USA
9 T010 C002 Credit 200.00 USD 21MAY2025 Pending USA
10 T001 C001 Cash 150.75 USD 22MAY2025 Complete USA


Step 4: Summary Statistics Using PROC MEANS

proc means data=money n mean median min max sum maxdec=2;

    class Transaction_Type Status;

    var Amount;

    title "Summary Statistics of Amount by Transaction Type and Status";

run;

Output:

Summary Statistics of Amount by Transaction Type and Status

The MEANS Procedure

Analysis Variable : Amount
Transaction_Type Status N Obs N Mean Median Minimum Maximum Sum
BankTransfer Complete 3 3 733.33 700.00 500.00 1000.00 2200.00
Cash Complete 3 3 96.92 80.00 60.00 150.75 290.75
  Failed 1 1 30.00 30.00 30.00 30.00 30.00
Credit Complete 2 2 185.00 185.00 120.00 250.00 370.00
  Failed 1 1 300.00 300.00 300.00 300.00 300.00
  Pending 1 1 200.00 200.00 200.00 200.00 200.00
DigitalWallet Complete 3 3 80.08 75.25 45.00 120.00 240.25
  Pending 1 1 99.50 99.50 99.50 99.50 99.50


Step 5: Frequency Counts Using PROC FREQ

proc freq data=money;

    tables Transaction_Type Status Transaction_Type*Status / norow nocol nopercent;

    title "Frequency Distribution of Transaction Types and Status";

run;

Output:

Frequency Distribution of Transaction Types and Status

The FREQ Procedure

Transaction_Type Frequency Cumulative
Frequency
BankTransfer 3 3
Cash 4 7
Credit 4 11
DigitalWallet 4 15


Status Frequency Cumulative
Frequency
Complete 11 11
Failed 2 13
Pending 2 15


Frequency
Table of Transaction_Type by Status
Transaction_Type Status
Complete Failed Pending Total
BankTransfer
3
0
0
3
Cash
3
1
0
4
Credit
2
1
1
4
DigitalWallet
3
0
1
4
Total
11
2
2
15


Step 6: Using PROC SQL to Summarize Data

proc sql;

    title "Total Transaction Amount by Country and Currency";

    select Country, Currency, 

           count(*) as Num_Transactions,

           sum(Amount) format=dollar12.2 as Total_Amount,

           mean(Amount) format=dollar12.2 as Avg_Amount

    from money

    where Status = "Completed"

    group by Country, Currency

    order by Total_Amount desc;

quit;

Output:

Total Transaction Amount by Country and Currency

Country Currency Num_Transactions Total_Amount Avg_Amount
USA USD 6 $1,676.00 $279.33
India INR 2 $820.00 $410.00
France EUR 2 $560.00 $280.00
UK GBP 1 $45.00 $45.00


Step 7: Creating a Macro for Repeated Summary

%macro summarize_transactions(type=);

    proc sql;

        title "Summary for Transaction Type: &type";

        select Status, 

               count(*) as Num_Transactions,

               sum(Amount) format=dollar12.2 as Total_Amount,

               mean(Amount) format=dollar12.2 as Avg_Amount

        from money

        where Transaction_Type = "&type"

        group by Status

        order by Total_Amount desc;

    quit;

%mend summarize_transactions;


%summarize_transactions(type=Cash);

Output:

Summary for Transaction Type: Cash

Status Num_Transactions Total_Amount Avg_Amount
Completed 3 $290.75 $96.92
Failed 1 $30.00 $30.00


%summarize_transactions(type=Credit);

Output:

Summary for Transaction Type: Credit

Status Num_Transactions Total_Amount Avg_Amount
Completed 2 $370.00 $185.00
Failed 1 $300.00 $300.00
Pending 1 $200.00 $200.00


%summarize_transactions(type=DigitalWallet);

Output:

Summary for Transaction Type: DigitalWallet

Status Num_Transactions Total_Amount Avg_Amount
Completed 3 $240.25 $80.08
Pending 1 $99.50 $99.50


Step 8: Advanced Data Manipulation with SQL and Macros

Step 8a: Flagging Large Transactions Using Macro

%macro flag_large_transactions(data=money, out=money_flagged, threshold=200);

    data &out;

        set &data;

        if Amount > &threshold then Large_Transaction = "Yes";

        else Large_Transaction = "No";

    run;

    

    proc print data=&out;

        title "Transactions Flagged by Amount Threshold &threshold";

    run;

%mend flag_large_transactions;

%flag_large_transactions(threshold=100);

Output:

Transactions Flagged by Amount Threshold 100

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country Large_Transaction
1 T001 C001 Cash 150.75 USD 22MAY2025 Completed USA Yes
2 T002 C002 Credit 250.00 USD 15MAY2025 Completed USA Yes
3 T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA No
4 T004 C001 BankTransfer 500.00 EUR 05MAY2025 Completed France Yes
5 T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK No
6 T006 C005 Credit 120.00 INR 17MAY2025 Completed India Yes
7 T007 C006 DigitalWallet 75.25 USD 20MAY2025 Completed USA No
8 T008 C007 BankTransfer 1000.00 USD 18MAY2025 Completed USA Yes
9 T009 C008 Cash 60.00 EUR 12MAY2025 Completed France No
10 T010 C002 Credit 200.00 USD 21MAY2025 Pending USA Yes
11 T011 C009 DigitalWallet 45.00 GBP 22MAY2025 Completed UK No
12 T012 C010 BankTransfer 700.00 INR 23MAY2025 Completed India Yes
13 T013 C011 Cash 80.00 USD 25MAY2025 Completed USA No
14 T014 C012 Credit 300.00 USD 27MAY2025 Failed USA Yes
15 T015 C001 DigitalWallet 120.00 USD 28MAY2025 Completed USA Yes


%flag_large_transactions(threshold=500);

Output:

Transactions Flagged by Amount Threshold 500

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country Large_Transaction
1 T001 C001 Cash 150.75 USD 22MAY2025 Completed USA No
2 T002 C002 Credit 250.00 USD 15MAY2025 Completed USA No
3 T003 C003 DigitalWallet 99.50 USD 01MAY2025 Pending USA No
4 T004 C001 BankTransfer 500.00 EUR 05MAY2025 Completed France No
5 T005 C004 Cash 30.00 GBP 10MAY2025 Failed UK No
6 T006 C005 Credit 120.00 INR 17MAY2025 Completed India No
7 T007 C006 DigitalWallet 75.25 USD 20MAY2025 Completed USA No
8 T008 C007 BankTransfer 1000.00 USD 18MAY2025 Completed USA Yes
9 T009 C008 Cash 60.00 EUR 12MAY2025 Completed France No
10 T010 C002 Credit 200.00 USD 21MAY2025 Pending USA No
11 T011 C009 DigitalWallet 45.00 GBP 22MAY2025 Completed UK No
12 T012 C010 BankTransfer 700.00 INR 23MAY2025 Completed India Yes
13 T013 C011 Cash 80.00 USD 25MAY2025 Completed USA No
14 T014 C012 Credit 300.00 USD 27MAY2025 Failed USA No
15 T015 C001 DigitalWallet 120.00 USD 28MAY2025 Completed USA No


Step 8b: Creating a Summary Table of Large Transactions by Country

%macro summarize_large_transactions(data=money_flagged);

    proc sql;

        title "Summary of Large Transactions by Country";

        select Country, Large_Transaction, 

               count(*) as Num_Transactions,

               sum(Amount) format=dollar12.2 as Total_Amount

        from &data

        where Large_Transaction = "Yes"

        group by Country, Large_Transaction

        order by Total_Amount desc;

    quit;

%mend summarize_large_transactions;


%summarize_large_transactions(data=money_flagged);

Output:

Summary of Large Transactions by Country

Country Large_Transaction Num_Transactions Total_Amount
USA Yes 9 $2,275.50
India Yes 2 $820.00
France Yes 2 $560.00
UK Yes 2 $75.00


Step 9: Date-based Aggregation Using PROC SQL

proc sql;

    title "Monthly Transaction Summary";

    create table monthly_summary as

    select year(Transaction_Date) as Year,

           month(Transaction_Date) as Month,

           count(*) as Num_Transactions,

           sum(Amount) format=dollar12.2 as Total_Amount,

           mean(Amount) format=dollar12.2 as Avg_Amount

    from money

    where Status = "Completed"

    group by calculated Year, calculated Month

    order by Year, Month;

quit;


proc print data=monthly_summary;

    title "Monthly Summary of Completed Transactions";

run;

Output:

Monthly Summary of Completed Transactions

Obs Year Month Num_Transactions Total_Amount Avg_Amount
1 2025 5 11 $3,101.00 $281.91


Step 10: Generating a Report of Failed Transactions

proc print data=money;

    where Status = "Failed";

    title "Report of Failed Transactions";

run;

Output:

Report of Failed Transactions

Obs Transaction_ID Customer_ID Transaction_Type Amount Currency Transaction_Date Status Country
5 T005 C004 Cash 30 GBP 10MAY2025 Failed UK
14 T014 C012 Credit 300 USD 27MAY2025 Failed USA


Step 11: Visualizing the Data with PROC SGPLOT 

proc sgplot data=money;

    vbar Transaction_Type / group=Status groupdisplay=cluster datalabel;

    title "Transaction Counts by Type and Status";

run;

Log:
NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           2.93 seconds
      cpu time            0.46 seconds

NOTE: Listing image output written to SGPlot1.png.
NOTE: There were 15 observations read from the data set WORK.MONEY.


PRACTICE AND COMMENT YOUR CODE: 

--->PLEASE FOLLOW OUR BLOG FOR MORE UPDATES.

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

PLEASE FOLLOW OUR INSTAGRAM PAGE CLICK HERE

Comments