topic Re: how to use macro to sort multiple datasets then to merge in SAS Programming

how to use macro to sort multiple datasets then to merge

Bal23 — Tue, 05 Jul 2016 16:27:28 GMT

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.

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

ballardw — Tue, 05 Jul 2016 19:29:15 GMT

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.

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

Bal23 — Tue, 05 Jul 2016 19:47:34 GMT

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.

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

ballardw — Tue, 05 Jul 2016 22:49:05 GMT

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.

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

LinusH — Wed, 06 Jul 2016 08:17:20 GMT

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).

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

Bal23 — Wed, 06 Jul 2016 13:19:32 GMT

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