topic Re: Append columns in SAS Programming

Append columns

yoyong555 — Tue, 11 Aug 2020 00:06:35 GMT

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

Re: Append columns

novinosrin — Tue, 11 Aug 2020 00:24:53 GMT



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;

Re: Append columns

yoyong555 — Tue, 11 Aug 2020 00:43:23 GMT

Hi @novinosrin.
If the variables have different names (e.g. Zscore, Postzscore Prezscore), how does that affect the program?

Re: Append columns

novinosrin — Tue, 11 Aug 2020 00:47:45 GMT

change the

array t score:	;

to

array t zscore postzscore prezscore;

Re: Append columns

s_lassen — Tue, 11 Aug 2020 10:23:25 GMT

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;

Re: Append columns

Kurt_Bremser — Tue, 11 Aug 2020 10:48:11 GMT

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.

Re: Append columns

ghosh — Wed, 12 Aug 2020 16:05:19 GMT

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;

Re: Append columns

Tom — Wed, 12 Aug 2020 16:27:48 GMT

How does this apply to the question at hand where the values are being written inside the loop?

Re: Append columns

Kurt_Bremser — Wed, 12 Aug 2020 16:35:10 GMT

@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 😉