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?
Thanks.
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.
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
errors.
NOTE: There were 282812 observations read from the data set
Ie.mon.
NOTE: There were 12205 observations read from the data set
Ie.tue.
NOTE: There were 12161 observations read from the data set
Ie.wed.
NOTE: There were 9858 observations read from the data set
Ie.thu.
WARNING: The data set Ie.MERGED may be incomplete. When this
step was stopped there were 302460 observations and
242 variables.
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.
Thanks. Would you please provide a sample code for this?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.