proc transpose: using more that one prefix

[Anita_n]

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?

[PaigeMiller]

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 ...

[Kurt_Bremser]

Since this is less a usable dataset, but a report, consider using PROC REPORT.

[Anita_n]

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.

[Tom]

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.

[Ksharp · Accepted solution]

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;

[PaigeMiller]

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;