BookmarkSubscribeRSS Feed
Bal23
Lapis Lazuli | Level 10

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.

 

5 REPLIES 5
ballardw
Super User

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.

 

Bal23
Lapis Lazuli | Level 10

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.

 

ballardw
Super User

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.

 

 

LinusH
Tourmaline | Level 20
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
Bal23
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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