DATA Step, Macro, Functions and more

how to use macro to sort multiple datasets then to merge

Reply
Super Contributor
Posts: 336

how to use macro to sort multiple datasets then to merge

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.

 

Super User
Posts: 10,548

Re: how to use macro to sort multiple datasets then to merge

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.

 

Super Contributor
Posts: 336

Re: how to use macro to sort multiple datasets then to merge

[ Edited ]

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.

 

Super User
Posts: 10,548

Re: how to use macro to sort multiple datasets then to merge

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.

 

 

Super User
Posts: 5,260

Re: how to use macro to sort multiple datasets then to merge

Or store the data in a SPDE libref which will remove the need for pre sorting (it will be sorted implicitly when the data set encounter a BY statement).
Data never sleeps
Super Contributor
Posts: 336

Re: how to use macro to sort multiple datasets then to merge

Thanks. Would you please provide a sample code for this?

Ask a Question
Discussion stats
  • 5 replies
  • 359 views
  • 0 likes
  • 3 in conversation