topic Re: Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array? in SAS Programming

Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array?

anonymous_user — Tue, 28 Sep 2021 16:43:19 GMT

How do I switch the order of 2 rows (_n_=8 & _n_=9) without compromising the rest of the dataset?

proc sort data=exsrc2 out=exsrc3;
	by STUDYID USUBJID EXTRT EXSTDTC;
run;

data exsrc4;
	EXSEQ=0;
	format &varlist;
	do until (last.USUBJID);
	set exsrc3;
	by USUBJID;
	EXSEQ+1;
	output;
	end;
run;

I feel like the solution should be really easy (PROC SQL; update? or a data step & multiple DO loops) but I'm struggling. Please help!! Thank you.

Re: Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array?

ballardw — Tue, 28 Sep 2021 16:54:48 GMT

@anonymous_user wrote:

How do I switch the order of 2 rows (_n_=8 & _n_=9) without compromising the rest of the dataset?
proc sort data=exsrc2 out=exsrc3;
	by STUDYID USUBJID EXTRT EXSTDTC;
run;

data exsrc4;
	EXSEQ=0;
	format &varlist;
	do until (last.USUBJID);
	set exsrc3;
	by USUBJID;
	EXSEQ+1;
	output;
	end;
run;
I feel like the solution should be really easy (PROC SQL; update? or a data step & multiple DO loops) but I'm struggling. Please help!! Thank you.

One way using Firstobs and Obs= dataset options:

data example;
   do row= 1 to 15;
   output;
   end;
run;

data want;
  set example (firstobs=1 obs=7)
      example (firstobs=9 obs=9)
      example (firstobs=8 obs=8)
      example (firstobs=10)
  ;
run;

Firstobs sets the observation number (row or _n_ value) as the first read, obs= sets the observation number to end getting data from the data set. With no Obs set the default value (max) means the remainder of the data is read.

Note the OBS= is the observation number, not the number of records to read. So when Firstobs=Obs value then one record is read.

Re: Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array?

Tom — Tue, 28 Sep 2021 18:54:23 GMT

Why?

From your photograph is looks like there is a variable EXSEQ that is controlling the order with-in the higher order sorting.

So is the real question how to change EXSEQ=1 to 2 and EXSEQ=2 to 1 for that given subject?

Re: Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array?

anonymous_user — Tue, 28 Sep 2021 19:18:27 GMT

THANK YOU so much!!! It worked. OBS vs FIRSTOBS is such a beginning programming topic --- I forgot about it.

Re: Switching Order of 2 Data Rows: PROC SQL or DO LOOP or array?

anonymous_user — Tue, 28 Sep 2021 19:21:08 GMT

Thank you for pointing that out! I followed the metadata instructions for creating the EXSEQ variable, so those programming instructions don't line up with the original dataset. I was trying to recreate the "ex" dataset. That's why I asked how to flip the order of 2 rows distinctly.

I appreciate the help highlighting the constraint of the EXSEQ ordering---gah!