Hi Everyone,
I want to automate the below result(no hardcoding).
I have three datasets namely Policy, Policy_Fact, Account_Info. Below are the columns in each table.
Policy- Pol_num_txt
Policy_Fact- Pol_num_txt
Service_tax
Stamp_duty
Sales_Tax
Account_Info- Ac_code
Col_Name of Policy_Fact
Ac_code column is mapped against the columns(except pol_num_txt) present in Policy_Fact. In the output dataset, I want only two columns pol_num_txt and Amount.
For those policies which are present in Policy table, I want to create multiple observations as per the Ac_Code present in Account_Info table.
Under the Amount column appropriate value from Policy_Fact table is expected.
For eg.
Policy Policy_Fact Account_Info
pol_num_txt pol_num_txt Service_Tax Stamp_duty Sales_tax Ac_code Col_Name
101 101 12 10 8 A11 Service_Tax
102 102 12 10 8 A12 Sales_Tax
103 12 10 8
Output Dataset
pol_num_txt Amount
101 12
101 8
102 12
102 8
Your help is highly appreciated. Thanks.
How about using the following steps?
1. Create two new datasets from Policy_Fact table by using Querybuilder. The first dataset will contain two columns, pol_num_txt and Service_Tax. Assign new column name, Amount, to Service_Tax. The second dataset will contain two columns, pol_num_txt and Sales_tax. Assign new column name, Amount, to Sales_tax. So, you will have two identical datasets which have two columns, pol_num_txt and Amount. The Amount column on the first dataset actually contains data of Service_Tax, and the Amount column on the second dataset actully contains data of Sales_tax. See examples below.
First Dataset Second Dataset
pol_num_txt Amount pol_num_txt Amount
101 12 101 8
102 12 102 8
103 12 103 8
2. Open the first dataset and run Append Table. Append Table is located under Data function. Append Table enables you to concatenate the first dataset and second dataset. After completing Append Table, sort the table by pol_num_txt.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.