BookmarkSubscribeRSS Feed
Teamtim
Fluorite | Level 6

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.

 

 

8 REPLIES 8
Reeza
Super User
You can include multiple values in the BY and ID statements. Post the code you tried if you're having issues.
Teamtim
Fluorite | Level 6

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
Super User
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
Super User

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
Opal | Level 21
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
Fluorite | Level 6
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
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1958 views
  • 0 likes
  • 5 in conversation