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.
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.
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 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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.