topic Re: Do loop skipping parts of first observation in SAS Programming

Do loop skipping parts of first observation

SasPerson85 — Tue, 08 Oct 2019 17:34:59 GMT

I have a dataset that looks like this:

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

fname

lname

2264

Amy_Runyon

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

Andrea

Turner

2264

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

Susan

Bowlin

What my current dataset looks like after the loop runs:

fname_1

fname_2

lname_1

lname_2

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

2264

Andrea

Turner

Amy_Runyon

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

So it will break out the fname and lname columns into fname_1, fname_2 etc.. and lname_1, lname_2 etc..

My issue is my current code is doing what I want for the second observation, but not completely for the first observation.

I need the final dataset to look like:

fname_1

fname_2

lname_1

lname_2

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

2264

Andrea

Susan

Turner

Bowlin

Amy_Runyon

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

The code for the loop:

data final_3;
	retain id;
	array arrayVar1(*) $&maxFNAME.. fname_1 - fname_&maxObsname;
	array arrayVar2(*) $&maxLNAME.. lname_1 - lname_&maxObsname;

	do i = 1 by 1 until (last.id);
		set final_2_1;
		by id;
		arrayVar1(i) = FNAME;
		arrayVar2(i) = LNAME;
		output;
	end;
	drop i fname lname;
run;

Any ideas?

Thanks in advance

Re: Do loop skipping parts of first observation

Astounding — Tue, 08 Oct 2019 17:56:51 GMT

It looks like you placed the OUTPUT statement too soon in the program. Move it to after the END statement (or else just remove it entirely).

Re: Do loop skipping parts of first observation

SasPerson85 — Tue, 08 Oct 2019 18:00:57 GMT

Thanks for the reply.

I just tried your suggestion, but it seems to have gotten rid of the first observation:

fname_1

fname_2

lname_1

lname_2

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

Adjusted code for this:

data final_3;
	retain id;
	array arrayVar1(*) $&maxFNAME.. fname_1 - fname_&maxObsname;
	array arrayVar2(*) $&maxLNAME.. lname_1 - lname_&maxObsname;

	do i = 1 by 1 until (last.id);
		set final_2_1;
		by id;
		arrayVar1(i) = FNAME;
		arrayVar2(i) = LNAME;
	end;
	output;
	drop i fname lname;
run;

Also doing this:

data final_3;
	retain id;
	array arrayVar1(*) $&maxFNAME.. fname_1 - fname_&maxObsname;
	array arrayVar2(*) $&maxLNAME.. lname_1 - lname_&maxObsname;

	do i = 1 by 1 until (last.id);
		set final_2_1;
		by id;
		arrayVar1(i) = FNAME;
		arrayVar2(i) = LNAME;
		output;
	end;
	output;
	drop i fname lname;
run;

Results in:

fname_1

fname_2

lname_1

lname_2

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

2264

Andrea

Turner

Amy_Runyon

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

Re: Do loop skipping parts of first observation

SasPerson85 — Tue, 08 Oct 2019 18:02:54 GMT

Also removing the output statement completely results in:

fname_1

fname_2

lname_1

lname_2

username

duration

title

coursepre

numpart

instruct_type

comment

dty_date

dtm_date

dtd_date

coursenum

section

2264

Andrea

Susan

Turner

Bowlin

Beth_Farwell

4 or more hours

Intro to the St. John's Bible Heritage Edition

Demonstration

2019

February

Re: Do loop skipping parts of first observation

Astounding — Tue, 08 Oct 2019 18:10:05 GMT

OK, it's becoming a little clearer. You will need to move through the data set twice:

data final_3;
	array arrayVar1(*) $&maxFNAME.. fname_1 - fname_&maxObsname;
	array arrayVar2(*) $&maxLNAME.. lname_1 - lname_&maxObsname;

	do i = 1 by 1 until (last.id);
		set final_2_1;
		by id;
		arrayVar1(i) = FNAME;
		arrayVar2(i) = LNAME;
	end;
	do i = 1 by 1 until (last.id);
		set final_2_1;
		by id;;
		output;
	end;
	drop i fname lname;
run;

The top loop grabs the values into the _1 and _2 variables, and the bottom loop re-reads the same observations and outputs them with the "grabbed" values.

Re: Do loop skipping parts of first observation

SasPerson85 — Tue, 08 Oct 2019 18:12:29 GMT

Ah that makes sense! It worked wonderfully as well, thank you very much for your assistance!

Re: Do loop skipping parts of first observation

Tom — Tue, 08 Oct 2019 18:21:27 GMT

I cannot figure out from your question what you are trying to do.

Are you trying to turn one observation into many?

Or trying to turn many observations into one?