Hi everyone.
I need help with programming.
My data looks like Table1.
I need to be extract the scores of members only (Member = Y) and produce a data which contains two columns like in Table 2.
Thank you for the assistance.
Yoyong
Table 1.
Score1 | Score2 | Score3 | Member |
10 | 8 | 34 | Y |
12 | 5 | 12 | N |
15 | 18 | 10 | Y |
8 | 16 | 0 | Y |
15 | 12 | 5 | Y |
12 | 0 | 2 | N |
6 | 8 | 15 | Y |
10 | 13 | 21 | Y |
Table 2.
Score_group | Score |
1 | 10 |
1 | 15 |
1 | 8 |
1 | 15 |
1 | 6 |
1 | 10 |
2 | 8 |
2 | 18 |
2 | 16 |
2 | 12 |
2 | 8 |
2 | 13 |
3 | 34 |
3 | 10 |
3 | 0 |
3 | 5 |
3 | 15 |
3 | 21 |
data have;
input Score1 Score2 Score3 Member $;
cards;
10 8 34 Y
12 5 12 N
15 18 10 Y
8 16 0 Y
15 12 5 Y
12 0 2 N
6 8 15 Y
10 13 21 Y
;
data temp/view=temp;
set have;
where Member='Y';
array t score: ;
do over t;
Score_group=_i_;
score=t;
output;
end;
keep Score_group score;
run;
proc sort data=temp out= want;
by Score_group;
run;
change the
array t score: ;
to
array t zscore postzscore prezscore;
While the (officially deprecated, but still functioning) DO OVER used by @novinosrin is smart in some cases, here it is not. Instead use the actual variable SCORE_GROUP to index the array:
data have;
input Score1 Score2 Score3 Member $;
cards;
10 8 34 Y
12 5 12 N
15 18 10 Y
8 16 0 Y
15 12 5 Y
12 0 2 N
6 8 15 Y
10 13 21 Y
;run;
data want;
set have;
where member='Y';
array scores(*) score1-score3;
do score_group=1 to dim(scores);
score=scores(score_group);
output;
end;
keep score_group score;
run;
proc sort data=want;
by score_group;
run;
Using DO OVER and _I_ yields a different result than using a standard iterative DO loop:
data test;
array t t1 - t3;
do over t;
x = _i_;
end;
do y = 1 to dim(t);
t{y} = 1;
end;
run;
because the iterator is incremented past the TO value before the loop stops.
How does this apply to the question at hand where the values are being written inside the loop?
@Tom wrote:
How does this apply to the question at hand where the values are being written inside the loop?
Missed the OUTPUT. Must have been before the coffee 😉
If the variables have different names (e.g. Zscore, Postzscore Prezscore) use the scan function to extract numbers for the score_group formula in table2
data table1 (drop=member);
input Score1 Score2 Score3 Member: $1.;
if trim(member) in ("Y");
id=_n_;
cards;
10 8 34 Y
12 5 12 N
15 18 10 Y
8 16 0 Y
15 12 5 Y
12 0 2 N
6 8 15 Y
10 13 21 Y
;
/* Transpose into one column */
proc transpose data=table1 out=long;
by id;
run;
data table2 (drop=_name_ id);
length score_group 4;
set long;
score_group=input(substr(_name_,6),1.);
rename col1=score;
run;
proc sort;
by score_group;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: