BookmarkSubscribeRSS Feed
marianthi86
Calcite | Level 5

Hi,

 

 dataAB  which is a vertical merge of data A and data B contains ID numbers 501-510 for data A and 501-510 for data B. Now i need to do a horizontal merge by ID of dataAB with baselinedata and it will not work. I believe i need to renumber my ID. How do I do that?

 

Data A ID: 501 502 503 504 505 506 507 508 509 510

Data B ID: 501 502 503 504 505 506 507 508 509 510

Baseline:  501 502 503 504 505 506 507 508 509 510 601 602 603 604 605

 

Code: *************Vertical Merge***********;
Data dataAB; set dataa datab;
run;
**************Horizontal Merge********;
data HorMerge2; merge dataAB baseline;
by ID;

 

 

 

Thanks.

9 REPLIES 9
PaigeMiller
Diamond | Level 26

@marianthi86 wrote:

 

Now i need to do a horizontal merge by ID of dataAB with baselinedata and it will not work.


Why doesn't it work? What happens? What is the error? Please tell us.

--
Paige Miller
art297
Opal | Level 21

Since dataAB isn't in id order, you have to sort it before merging. e.g.:

data dataa;
  input ID @@;
  cards;
501 502 503 504 505 506 507 508 509 510
;
Data datab;
  input ID @@;
  cards;
501 502 503 504 505 506 507 508 509 510
;
data baseline;
  input ID @@;
  cards;
501 502 503 504 505 506 507 508 509 510 601 602 603 604 605
;

 

/* Code: *************Vertical Merge***********; */
data dataAB;
  set dataa datab;
run;

/* **************Horizontal Merge********; */
proc sort data=dataAB;
  by ID;
run;

data HorMerge2; 
  merge dataAB baseline;
  by ID;
run;

Art, CEO, AnalystFinder.com

 

marianthi86
Calcite | Level 5
When i try run the syntax
data baseline24;
input ID @@;
cards;
501 502 503 504 505 506 507 508 509 510 601 602 603 604 605
;

I get the following message:



ERROR: You cannot open WORK.BASELINE24.DATA for output access with
member-level control because
WORK.BASELINE24.DATA is in use by you in resource environment ViewTable
Window.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

287 ;


can you please help me resolve this issue?

Thank you so much!
PaigeMiller
Diamond | Level 26

You can't use SAS code to modify or change a data set that is opened in ViewTable. Close the ViewTable window first.

--
Paige Miller
art297
Opal | Level 21

If you're using Enterprise Guide, particularly an older version of Enterprise Guide, just re-run the code. The error you received is explained in the following note: http://support.sas.com/kb/38/344.html

 

Art, CEO, AnalystFinder.com

 

marianthi86
Calcite | Level 5
Now that i resorted my dataA, dataB and data baseline ID with the syntax
you provided all my other columns were deleted from the data. How do i
resort my data without deleting the other columns?
art297
Opal | Level 21

Sorting, in itself, doesn't delete data. You never mentioned other columns. In order for anyone to help you'd have to show us your data and the code you ran.

 

My initial guess is that you had created data files that contained your actual data, but overwrote them with the datasets created by the code I posted.

 

Art, CEO, AnalystFinder.com

 

marianthi86
Calcite | Level 5
I apologize. I have included attachments of my data. And yes your guess is
correct. how can i sort them by ID?

Thank you.
art297
Opal | Level 21

I didn't see any attachments. Regardless, to sort, simply use proc sort. You only have to sort what you called the vertical merge data and, if your baseline data isn't in ID order, you'd have to sort that as well before doing the horizontal merge.

 

Art, CEO, AnalystFinder.com

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