Transposing table and keeping first entry of a column

[Teamtim]

Hi,

Please I need help on how best to handle the following problem.

I have a table in this format

ID	COUNTRY	MONTH	MATHSCORE	ENGSCORE
1	UK	1	10	3
1	UK	2	15	8
1	UK	3	24	7
2	US	2	15	10
2	US	4	12	18
3	FL	1	15	15
3	FL	2	16	18
3	FL	3	17	16
3	FL	4	15	16

The output I want is  below

ID	COUNTRY	MONTH	MATHSCORE1	MATHSCORE2	MATHSCORE3	MATHSCORE4	ENGSCORE1	ENGSCORE2	ENGSCORE3	ENGSCORE4
1	UK	1	10	15	24		3	8	7
2	US	2	15			12		10		18
3	FL	1	15	16	17	15	15	18	16	16

I could transpose for mathscore, then for engscore and then merge the data but my problem is how to keep just the month value for each of the id.

[Reeza]

You can include multiple values in the BY and ID statements. Post the code you tried if you're having issues.

[Teamtim]

data tic;
input id country$ month math;
datalines;
1 uk 1 10
1 uk 2 15
1 uk 3 24
2 us 2 15
2 us 4 12
3 fl 1 15
3 fl 2 16
3 fl 3 17
3 fl 4 15
;
run;
proc sort data=tic;
by id;
run;

data tot(drop=month math);
retain month1-month4 math1-math4;
array tat{4} month1-month4;
array kat{4} math1-math4;
set tic;
by id;
if first.id then do;
i=1;
do j=1 to 4;
tat{j}=.;
kat{j}=.;
end;
end;
tat(i)=month;
kat(i)=math;
if last.id then output;
i+1;
run;

The observations are shifted to the left.I can't seem to have them in the proper cell.

[Reeza]

You need to add a RETAIN statement and you need to set them all to missing each whenever you start a new group.

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

[ballardw]

Perhaps you could describe what you are going to do with the resultant dataset. It may be that the transformation is not even needed.

[art297]

proc transpose data=have out=want (where=(not missing(_name_))) prefix=mathscore_;
  by id country;
  var mathscore;
  id month;
  copy month;
run;

Art, CEO, AnalystFinder.com

[Teamtim]

I want to use data step. See my code below but there is problem with the missing values

data tic;
input id country$ month math;
datalines;
1 uk 1 10
1 uk 2 15
1 uk 3 24
2 us 2 15
2 us 4 12
3 fl 1 15
3 fl 2 16
3 fl 3 17
3 fl 4 15
;
run;
proc sort data=tic;
by id;
run;

data tot(drop=month math);
retain month1-month4 math1-math4;
array tat{4} month1-month4;
array kat{4} math1-math4;
set tic;
by id;
if first.id then do;
i=1;
do j=1 to 4;
tat{j}=.;
kat{j}=.;
end;
end;
tat(i)=month;
kat(i)=math;
if last.id then output;
i+1;
run;

[Ksharp]

data have;
infile cards expandtabs truncover;
input ID	COUNTRY $	MONTH	MATHSCORE	ENGSCORE ;
cards;
1   	UK	1	     10         	      3
1   	UK	2	     15         	      8
1   	UK	3	      24       	       7
2   	US	2	      15       	      10
2   	US	4	      12       	      18
3   	FL	1	        15       	     15
3   	FL	2	       16       	      18
3   	FL	3	        17       	      16
3   	FL	4	        15       	      16
;
run;

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 country;
output out=temp(drop=_type_ _freq_) idgroup(out[&n] (MATHSCORE ENGSCORE)=);
run;

data temp1;
 set have(keep=id country month);
 by id country;
 if first.country;
run;
data want;
 merge temp1 temp;
 by id country;
run;