topic Re: assign new ID numbers to my observations on sas? in SAS Programming

assign new ID numbers to my observations on sas?

marianthi86 — Sun, 11 Mar 2018 18:56:41 GMT

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.

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

PaigeMiller — Sun, 11 Mar 2018 19:15:44 GMT

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

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

art297 — Sun, 11 Mar 2018 19:27:12 GMT

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

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

marianthi86 — Sun, 11 Mar 2018 19:59:34 GMT

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!

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

PaigeMiller — Sun, 11 Mar 2018 20:07:36 GMT

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

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

art297 — Sun, 11 Mar 2018 20:10:13 GMT

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

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

marianthi86 — Sun, 11 Mar 2018 20:17:34 GMT

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?

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

art297 — Sun, 11 Mar 2018 20:27:08 GMT

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

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

marianthi86 — Sun, 11 Mar 2018 20:38:34 GMT

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

Thank you.

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

art297 — Sun, 11 Mar 2018 21:15:20 GMT

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