Dear all,
if I have a dataset that looks like this
data test_resullts;
input id month $3. maths english physics biology;
datalines;
1 jan 50 60 80 100
1 feb 60 75 60 50
1 mar 40 80 65 90
1 apr 55 99 60 85
2 jan 77 85 90 65
2 feb 65 55 75 100
2 mar 90 70 50 88
2 apr 78 50 80 55
3 jan 77 85 90 65
3 feb 65 55 75 100
3 mar 90 70 50 88
3 apr 78 50 80 55
;
run;
I want a result that looks like this:
If I use proc transpose , how can I use more than one prefix in my code, I was thinking I can use may something like
maths--biology (to avoid listing the names of all the variables if I have more than 15 variables)
proc transpose data=test_resullts out=results_wide prefix=maths_ let;
by id;
id month;
var maths english physics biology;
run;
any help?
data test_resullts;
input id month $3. maths english physics biology;
datalines;
1 jan 50 60 80 100
1 feb 60 75 60 50
1 mar 40 80 65 90
1 apr 55 99 60 85
2 jan 77 85 90 65
2 feb 65 55 75 100
2 mar 90 70 50 88
2 apr 78 50 80 55
3 jan 77 85 90 65
3 feb 65 55 75 100
3 mar 90 70 50 88
3 apr 78 50 80 55
;
run;
proc transpose data=test_resullts out=temp1;
by id month notsorted;
var maths english physics biology;
run;
proc sort data=temp1;by id _name_;run;
proc transpose data=temp1 out=want delimiter=_;
by id;
id _name_ month;
var col1;
run;
Typically, doing a transpose like this is not a good idea. It is not a good idea to have calendar information in variable names. Why do you need this? What will you do with this data once you achieve this transpose?
If all you want is an output table in this wide layout, PROC REPORT can do this without the need for PROC TRANSPOSE.
Also, it is almost never a good idea to have months named 'jan','feb',... etc. because then any sorting of the months will put 'apr' first and 'aug' second and most people don't want that. Month's should be valid SAS date values, or numbers such as 1 2 3 ...
Since this is less a usable dataset, but a report, consider using PROC REPORT.
Thanks for the reply, the real dataset doesn't contain dates in the variablename. This is just an example to illustrate what I want.
How does proc report do that? I have never used proc report before. Let me check the syntax.
In PROC REPORT use an ACROSS variable.
data test_results;
input id month $3. math english physics biology;
datalines;
1 jan 50 60 80 100
1 feb 60 75 60 50
1 mar 40 80 65 90
1 apr 55 99 60 85
2 jan 77 85 90 65
2 feb 65 55 75 100
2 mar 90 70 50 88
2 apr 78 50 80 55
3 jan 77 85 90 65
3 feb 65 55 75 100
3 mar 90 70 50 88
3 apr 78 50 80 55
;
proc report data=test_results;
column id (math english physics biology),month;
define id / group;
define month / across ' ';
run;
PS Fixed a couple of typos in the data step. Remove the extra L in the dataset name. Removed the S from the variable that holds the score for the math class. I assume there was only one math class since there was only one math score per id/month.
data test_resullts;
input id month $3. maths english physics biology;
datalines;
1 jan 50 60 80 100
1 feb 60 75 60 50
1 mar 40 80 65 90
1 apr 55 99 60 85
2 jan 77 85 90 65
2 feb 65 55 75 100
2 mar 90 70 50 88
2 apr 78 50 80 55
3 jan 77 85 90 65
3 feb 65 55 75 100
3 mar 90 70 50 88
3 apr 78 50 80 55
;
run;
proc transpose data=test_resullts out=temp1;
by id month notsorted;
var maths english physics biology;
run;
proc sort data=temp1;by id _name_;run;
proc transpose data=temp1 out=want delimiter=_;
by id;
id _name_ month;
var col1;
run;
How to make the months sort properly, so JAN comes first instead of APR coming first. Use actual SAS dates, use the ORDER=INTERNAL option in PROC REPORT, use the MONNAME. format if desired.
data test_results;
input id month :date9. math english physics biology;
datalines;
1 01JAN2022 50 60 80 100
1 01FEB2022 60 75 60 50
1 01MAR2022 40 80 65 90
1 01APR2022 55 99 60 85
2 01JAN2022 77 85 90 65
2 01FEB2022 65 55 75 100
2 01MAR2022 90 70 50 88
2 01APR2022 78 50 80 55
;
proc report data=test_results;
column id (math english physics biology),month;
define id / group;
define month / across ' ' order=internal format=monname3.;
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.