07-05-2016 12:27 PM
I have multiple datasets to merge but first I need to sort by id
is there an efficient way to use macro to do this? And to keep index so that I can see which record from which previous dataset?
07-05-2016 03:29 PM
How many is "many datasets"? Are they named in anyway that they are amenable to simple loop processing such as set1, set2, set3, ...setn?
By "keep index" do you mean a way to know if the resulting data set had contributions from different datasets?
If so here is one example using the IN dataset option:
data work.one; input id; datalines; 1 3 5 10 ; data work.two; input id; datalines; 2 3 5 ; run; data work.merged; merge work.one (in=one) work.two (in=two) ; by id; InOne=one; InTwo=two; run;
The final data set has a variable that has a value of one indicating that the given dataset contributed and zero otherwise. Since you have multiple datasets that may contribute to any given ID result then one variable per dataset. Notice that for the Id values of 1 and 10 that InOne is 1 and InTwo is 0. So only dataset work.one contributed to the result. For Id 3 and 5 both InOne and InTwo are 1 indicating both of the sets contributed. And for Id two only work.two contributed.
07-05-2016 03:43 PM - edited 07-05-2016 03:47 PM
Thank you very much.
There is an error message below
ERROR: BY variables are not properly sorted on data set Ie.tue.
NOTE: The SAS System stopped processing this step because of
NOTE: There were 282812 observations read from the data set
NOTE: There were 12205 observations read from the data set
NOTE: There were 12161 observations read from the data set
NOTE: There were 9858 observations read from the data set
WARNING: The data set Ie.MERGED may be incomplete. When this
step was stopped there were 302460 observations and
07-05-2016 06:49 PM
With 4 datasets shown (and with 7 possibly implied by the day of the week name) I wouldn't bother to go to any macro to sort the data but they do need to be sorted before merging.
07-06-2016 04:17 AM