cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SasPerson85
Calcite | Level 5 SasPerson85
Calcite | Level 5
Go to Solution

Do loop skipping parts of first observation

Posted 10-08-2019 01:34 PM (1451 views)

I have a dataset that looks like this:

iidusernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsectionfnamelname
12264Amy_Runyon4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  AndreaTurner
22264Beth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  SusanBowlin

 

 

What my current dataset looks like after the loop runs:

idfname_1fname_2lname_1lname_2usernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsection
2264Andrea Turner Amy_Runyon4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  

 

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:

idfname_1fname_2lname_1lname_2usernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsection
2264AndreaSusanTurnerBowlinAmy_Runyon4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  

 

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

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star Astounding
PROC Star

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:09 PM (1402 views)  |   In reply to SasPerson85

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.

View solution in original post

0 Likes
6 REPLIES 6
Astounding
PROC Star Astounding
PROC Star

Re: Do loop skipping parts of first observation

Posted 10-08-2019 01:54 PM (1435 views)  |   In reply to SasPerson85

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

0 Likes
SasPerson85
Calcite | Level 5 SasPerson85
Calcite | Level 5

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:00 PM (1418 views)  |   In reply to Astounding

Thanks for the reply.

 

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

idfname_1fname_2lname_1lname_2usernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsection
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  

 

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:

idfname_1fname_2lname_1lname_2usernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsection
2264Andrea Turner Amy_Runyon4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  

 

0 Likes
SasPerson85
Calcite | Level 5 SasPerson85
Calcite | Level 5

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:02 PM (1414 views)  |   In reply to SasPerson85

Also removing the output statement completely results in:

idfname_1fname_2lname_1lname_2usernamedurationtitlecourseprenumpartinstruct_typecommentdty_datedtm_datedtd_datecoursenumsection
2264AndreaSusanTurnerBowlinBeth_Farwell4 or more hoursIntro to the St. John's Bible Heritage Edition 53Demonstration 2019February27  
0 Likes
Astounding
PROC Star Astounding
PROC Star

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:09 PM (1403 views)  |   In reply to SasPerson85

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.

0 Likes
SasPerson85
Calcite | Level 5 SasPerson85
Calcite | Level 5

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:12 PM (1398 views)  |   In reply to Astounding

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

0 Likes
Tom
Super User Tom
Super User

Re: Do loop skipping parts of first observation

Posted 10-08-2019 02:21 PM (1393 views)  |   In reply to SasPerson85

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?

0 Likes

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎10-08-2019 01:34 PM
  • 1452 views
  • 0 likes
  • 3 in conversation