DATA Step, Macro, Functions and more

assign new ID numbers to my observations on sas?

Reply
Occasional Contributor
Posts: 7

assign new ID numbers to my observations on sas?

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.

Respected Advisor
Posts: 3,269

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

@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
Super User
Posts: 8,216

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

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

 

Occasional Contributor
Posts: 7

Re: assign new ID numbers to my observations on sas?

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!
Respected Advisor
Posts: 3,269

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

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
Super User
Posts: 8,216

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

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

 

Occasional Contributor
Posts: 7

Re: assign new ID numbers to my observations on sas?

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?
Super User
Posts: 8,216

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

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

 

Occasional Contributor
Posts: 7

Re: assign new ID numbers to my observations on sas?

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

Thank you.
Super User
Posts: 8,216

Re: assign new ID numbers to my observations on sas?

Posted in reply to marianthi86

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

Ask a Question
Discussion stats
  • 9 replies
  • 132 views
  • 0 likes
  • 3 in conversation