data set

[Naveen1111]

Hi All,

As i m a new learner, need mentioned output without using transpose as through transpose i got the result. but i want the same results with different dataset process. Is there any other way to get the same output. I tried it but getting missing values and unable to remove that, will be glad if some also let me knw how to remove only specific missing values.

data stretch;
input subj $ time score;
cards;
001 1 7
001 2 6
001 3 5
001 4 5
001 5 4
002 1 8
002 2 7
002 3 6
002 4 6
002 5 6
003 1 8
003 2 7
003 3 6
003 4 6
003 5 5
;
run;

REQUIRED  OUTPUT

Obs subj S1 S2 S3 S4 S5

1      001   7    6 5 5 4
2      002   8    7 6 6 6
3      003   8   7 6 6 5

ALtERNATE METHOD WHICH I TRIED

second method(where in i didnt get the desired output)

proc sort data=stretch out=mm;
by subj time;
run;

data kik;
drop time score subj;
set mm;
if time=1 then S1=score;
if time=2 then S2=score;
if time=3 then S3=score;
if time=4 then S4=score;
if time=5 then S5=score;
run;

proc print data=kik;
run;

and getting output with period as mentioned earlier and unable to remove specific missing values.

S1 S2 S3 S4 S5

1 7 . . . .
2 . 6 . . .
3 . . 5 . .
4 . . . 5 .
5 . . . . 4
6 8 . . . .
7 . 7 . . .
8 . . 6 . .
9 . . . 6 .
10 . . . . 6
11 8 . . . .
12 . 7 . . .
13 . . 6 . .
14 . . . 6 .
15 . . . . 5

[mkeintz]

Unless you tell SAS otherwise the data step will output one record for each input record.  So you need a way to output one record for every 5 records (assuming you always have exactly 5 records per SUBJ).  And that consequently means you need to retain the relevant values from records 1 through 4 when you encounter record 5:

Have you learned yet about

1. SET with a BY statement, as a way to determine when you are at the start or end of a SUBJ group of records.
2. The RETAIN statement - as a way to carry forward values (which are otherwise automatically reset to missing).

The ARRAY statement as a way to subsequently map a value of SCORE into the appropriate S1, S2, ... S5 in one statement.

[Naveen1111]

COULD U SHARE THE CODING

[mkeintz]

---

@Naveen1111 wrote:
COULD U SHARE THE CODING

---

Could you answer my question?

Help me help you.

[Naveen1111]

1. What I learnt .. set is used to copy the data from other dataset.

2.Retain. its similar to var statement, where in var works in proc and retain wrks in dataset.

3. array..what i understand if u want to put any value in place of missing values in multiple variable then u can use array.

Thanks

[ballardw]

IF every subj has 5 or fewer times then this should work:

data kik;
   drop time score subj;
   set mm;
   by subj;
   retain s1- s5;
   if first.subj then call missing(s1,s2,s3,s4,s5);
   if time=1 then S1=score;
   if time=2 then S2=score;
   if time=3 then S3=score;
   if time=4 then S4=score;
   if time=5 then S5=score;
   if last.subj then output;
run;

Retain keeps the values of variables across iterations of the data step, call missing will set a list of variables to missing which you want to happen before each subj data is processed to keep from having values from the previous subj. Using BY processing creates special SAS temporary variable first and last that are true when the first or last record of a group is encountered, false otherwise. So that allows the missing for the first value of subject and to write output only when the last record of each subj is encountered.

HOWEVER if any SUBJ has fewer or more than 5 "times", or they have values other than 1 through 5, you may have unexpected results. maybe.

[Naveen1111]

many many thanks for ur support.. got the answer but need clarity about the coding as why did we use 

if first.subj then call missing(s1,s2,s3,s4,s5);

if last.subj then output;

also have learned another use of retain stmnt. 

[ballardw]

---

@Naveen1111 wrote:

many many thanks for ur support.. got the answer but need clarity about the coding as why did we use 

if first.subj then call missing(s1,s2,s3,s4,s5);

if last.subj then output;

also have learned another use of retain stmnt. 

---

Did you read the comments I provided after the code??? Already answered.

Or  run the code without those statements and see what happens, remove one at a time and see what each does.

[Naveen1111]

thanks a lot sir. Your 2nd advice helped me alot.

Just one question after using each statement one by one in different ways.... getting the required output without using the below mentioned statement.

if first.subj then call missing(s1,s2,s3,s4,s5);

 Could you tell me what is the purpose of using above statement.

[Reeza]

Transposing data tutorials, long to wide. 
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Your code fails to retain values across the rows so you need to add in the RETAIN concept and as well to reset at the end of each group so that values do not go across IDs.

---

@Naveen1111 wrote:

Hi All,

As i m a new learner, need mentioned output without using transpose as through transpose i got the result. but i want the same results with different dataset process. Is there any other way to get the same output. I tried it but getting missing values and unable to remove that, will be glad if some also let me knw how to remove only specific missing values.

data stretch;
input subj $ time score;
cards;
001 1 7
001 2 6
001 3 5
001 4 5
001 5 4
002 1 8
002 2 7
002 3 6
002 4 6
002 5 6
003 1 8
003 2 7
003 3 6
003 4 6
003 5 5
;
run;

REQUIRED  OUTPUT

Obs subj S1 S2 S3 S4 S5

1      001   7    6 5 5 4
2      002   8    7 6 6 6
3      003   8   7 6 6 5

ALtERNATE METHOD WHICH I TRIED

second method(where in i didnt get the desired output)

proc sort data=stretch out=mm;
by subj time;
run;

data kik;
drop time score subj;
set mm;
if time=1 then S1=score;
if time=2 then S2=score;
if time=3 then S3=score;
if time=4 then S4=score;
if time=5 then S5=score;
run;

proc print data=kik;
run;

and getting output with period as mentioned earlier and unable to remove specific missing values.

S1 S2 S3 S4 S5

1 7 . . . .
2 . 6 . . .
3 . . 5 . .
4 . . . 5 .
5 . . . . 4
6 8 . . . .
7 . 7 . . .
8 . . 6 . .
9 . . . 6 .
10 . . . . 6
11 8 . . . .
12 . 7 . . .
13 . . 6 . .
14 . . . 6 .
15 . . . . 5

---

[Naveen1111]

thanks for sharing the link. Where did you get this knowledge. i m sorry because learning from private institute and don't think they will share this deeply information.

Any suggestion to enhance this skills. 

[Reeza]

1. Build yourself a foundation - take the SAS Programming 1 course and do the SAS Certification prep
https://github.com/statgeek/SAS-Tutorials/blob/master/Base_Certification_Resource_Guide.md

2. Once you have a base you focus on learning specific skills using lexjansen.com Search any topic on here and find SAS related answers. I would advise you to pay attention to the dates of paper and use the most recent as things have changed a lot over time.

[Naveen1111]

many many thanks for ur support. Your guys are really helpful. The link which u shared is well designed and informative.