DATA Step, Macro, Functions and more

Transposing table and keeping first entry of a column

Reply
Occasional Contributor
Posts: 16

Transposing table and keeping first entry of a column

[ Edited ]

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.

 

 

Super User
Posts: 19,855

Re: Transposing table and keeping first entry of a column

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

Re: Transposing table and keeping first entry of a column

 
Occasional Contributor
Posts: 16

Re: Transposing table and keeping first entry of a column

[ Edited ]

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.

Super User
Posts: 19,855

Re: Transposing table and keeping first entry of a column

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/
Super User
Posts: 11,343

Re: Transposing table and keeping first entry of a column

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

PROC Star
Posts: 7,487

Re: Transposing table and keeping first entry of a column

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

 

Occasional Contributor
Posts: 16

Re: Transposing table and keeping first entry of a column

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;
Super User
Posts: 10,044

Re: Transposing table and keeping first entry of a column


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;
Ask a Question
Discussion stats
  • 8 replies
  • 209 views
  • 0 likes
  • 5 in conversation