BookmarkSubscribeRSS Feed
Xusheng
Obsidian | Level 7

Hi, I currently have 16 files with the similar names such as A1, A2, A3, etc, I also have a merge base file called B. What I want to do is merge 16 As files to B into one file. Suppose all the data is sorted and should be merged by date. I wrote the code for merging but doesn't work very well. The problem is some of the column variables in As are missing after merge. Does someone has any suggestion? Any help with this will be appreciated!

Here is the code I wrote.

DATA work.Merged; 
	MERGE work.A: work.B; 										
	BY Date; 
RUN; 
3 REPLIES 3
Reeza
Super User

You need to post more details. Especially around:

The problem is some of the column variables in As are missing after merge.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do you want to merge the datasets?   Sounds more like you want a set.  Anyways, if variables appear in multiple datasets then they will only appear once from the first dataset.  Also, your date might not match across all datasets, if so the variables which would come from that dataset will be blank in the instance where there is no match.  Provide test data - in the form of a datastep, and put in the {i} tool above post - for further help.

ballardw
Super User

Merge BY will take the values from the right most dataset on the merge statement with the named variable. So if work.B is missing a value for variable XYZZY, then the resulting data set will have a missing value for XYZZY.

 

Notice in this example that the values for the variable B all come from dataset work.two except for the value of A that is not in work.two.

data work.one;
   input a b;
datalines;
1 24
2 34
3 44
4 66
;
run;
data work.two;
   input a b;
datalines;
1  .
2  33
3  99
;
run;

data merged;
   merge work.one work.two;
   by a;
run;

Expand this to 17 files it can get very difficult to guess which data set may be causing a specific missing result.

 

Are the variables in the A datasets all supposed to be the same or do they have different variables in some?

If a variable only has values in one data set, then if it doesn't contribute to all records on the BY variable value then those records without a match will be missing as well.

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
  • 3 replies
  • 1634 views
  • 0 likes
  • 4 in conversation