topic Re: Help with duplicate data and creating varriable in SAS Programming

Help with duplicate data and creating varriable

cesararevalo — Fri, 25 Sep 2020 02:48:09 GMT

I have data that looks like this with duplicate ID.

ID is unique to an individual, test is dichotomous and score is continuous

id test score

1 2 3

1 1 2

1 2 9

2 1 5

3 2 3

4 1 3

4 1 8

I need It too look like this

id test score test2 score2 test3 score3

1 2 3 1 2 2 9

2 1 5

3 2 3

4 1 3 1 8

Re: Help with duplicate data and creating varriable

SK_11 — Fri, 25 Sep 2020 03:31:40 GMT

Hi cesararevalo

I hope this helps, here is the code which produces your output

data have;
input id test score;
datalines;
1 2 3
1 1 2
1 2 9
2 1 5
3 2 3
4 1 3
4 1 8
;
run;
proc transpose data=have out=want (drop=_NAME_) prefix=score;
By id;
var score ;
run;
proc transpose data=have out=want1 (drop=_NAME_) prefix=test;
By id;
var test ;
run;

data final;
retain id test1 score1 test2 score2 test3 score3 ;
merge want want1;
run;

The output is same as you wanted

id	test1	score1	test2	score2	test3	score3
1	2	3	1	2	2	9
2	1	5	.	.	.	.
3	2	3	.	.	.	.
4	1	3	1	8	.	.

Re: Help with duplicate data and creating varriable

Ksharp — Fri, 25 Sep 2020 11:46:30 GMT

data have;
input id test score;
datalines;
1 2 3
1 1 2
1 2 9
2 1 5
3 2 3
4 1 3
4 1 8
;
proc sql noprint;
select max(n) into : n 
 from (select count(*) as n from have group by id);
quit;
proc summary data=have ;
by id;
output out=want idgroup(out[&n] (test score)=);
run;

Re: Help with duplicate data and creating varriable

ballardw — Fri, 25 Sep 2020 14:47:10 GMT

Just to continue my obnoxious streak of asking this question: What exactly to you expect to do with this wide structure that can't be done with the existing structure?