DATA Step, Macro, Functions and more

Proc transpose and inner join

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Proc transpose and inner join

Hi,

 

I'm trying to inner join two datasets with 4 field in each. 3 of the fields are the same in both tables;

data set Nov: ACCTNB, ID, SourceACCT, Amount_Nov

data set Dec: ACCTNB, ID, SourceACCT, Amount_Dec

I'm trying o inner join these data sets to compare the amounts and see if  customers are paying different amount in two months. The problem is if there is one transaction for ID1 in Dec and three transactions for the same ID in Nov, SAS duplicates the Dec data three times to match Nov data.

how can I solve this problem?

 

Thank you


Accepted Solutions
Solution
‎01-31-2018 07:45 AM
Super User
Posts: 9,551

Re: Proc transpose and inner join

Name the amount variable same in all datasets.

Use a data step to concatenate all datasets, and use the indsname option, so that the sources are identified in the final dataset.

Now you can use by-group processing to summarize for each month, each account, etc

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 22,823

Re: Proc transpose and inner join

This means you have duplicates that you need to remove OR you need to add a condition to your join or WHERE to filter out the records. 

Determine why some have duplicates first, or how you would identify which records to keep and then we can help you out. 

 


parmis wrote:

Hi,

 

I'm trying to inner join two datasets with 4 field in each. 3 of the fields are the same in both tables;

data set Nov: ACCTNB, ID, SourceACCT, Amount_Nov

data set Dec: ACCTNB, ID, SourceACCT, Amount_Dec

I'm trying o inner join these data sets to compare the amounts and see if  customers are paying different amount in two months. The problem is if there is one transaction for ID1 in Dec and three transactions for the same ID in Nov, SAS duplicates the Dec data three times to match Nov data.

how can I solve this problem?

 

Thank you


 

Solution
‎01-31-2018 07:45 AM
Super User
Posts: 9,551

Re: Proc transpose and inner join

Name the amount variable same in all datasets.

Use a data step to concatenate all datasets, and use the indsname option, so that the sources are identified in the final dataset.

Now you can use by-group processing to summarize for each month, each account, etc

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Proc transpose and inner join

Posted in reply to KurtBremser

Could you explain more about "using the indsname option"?

I changed the Amount variable to be the same in both data sets, and I used data step to concatenate the data. now I have one data set with four variables.

Now, what would be the next step?

 

Thanks

Super User
Posts: 22,823

Re: Proc transpose and inner join

[ Edited ]
data class1; set sashelp.class; run;
data class2; set sashelp.class; run;

data class3; set sashelp.class; run;

data combined;
set class1-class3 indsname=source;
dsn_input = source;
run;

proc means data=combined n mean std max min median;
class dsn_input;
var weight;
run;


parmis wrote:

Could you explain more about "using the indsname option"?

I changed the Amount variable to be the same in both data sets, and I used data step to concatenate the data. now I have one data set with four variables.

Now, what would be the next step?

 

Thanks




Super User
Posts: 9,551

Re: Proc transpose and inner join


parmis wrote:

Could you explain more about "using the indsname option"?

I changed the Amount variable to be the same in both data sets, and I used data step to concatenate the data. now I have one data set with four variables.

Now, what would be the next step?

 

Thanks


I can see that, while I was resting in Morpheus' arms, @Reeza gave you a nice example. Use it as blueprint for your datasets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 115 views
  • 0 likes
  • 3 in conversation