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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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