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
- Get link
- X
- Other Apps
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:
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 |
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 |
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 |
|
|
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;
- Get link
- X
- Other Apps
Comments
Post a Comment