topic Could you please help me with the following doubt? in SAS Enterprise Guide

Could you please help me with the following doubt?

neha11_ch — Wed, 23 May 2012 16:56:39 GMT

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.

Re: Could you please help me with the following doubt?

ct — Thu, 24 May 2012 14:15:31 GMT

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.