topic Re: how to change the format of dataset in SAS Procedures

how to change the format of dataset

excelsas — Thu, 07 Apr 2011 04:03:42 GMT

I want to change the format of the dataset:
MY input dataset:

School Month StudentName Subject 1Test 2Test 3Test 4Test Total
ABC July-10 John Math 90 79 86 99 354
ABC July-10 John Science 92 89 99 91 371 ABC July-10 John History 78 69 82 79 308
ABC July-10 John English 81 79 83 82 325
ABC July-10 Becky Math 92 77 76 88 333
ABC July-10 Becky Science 67 76 65 83 291 ABC July-10 Becky History 78 69 76 79 302 ABC July-10 Becky English 90 79 80 81 330
ABC Dec-10 John Math 97 93 89 99 378
ABC Dec-10 John Science 80 89 92 91 352
ABC Dec-10 John History 70 77 76 80 303
ABC Dec-10 John English 80 69 78 81 308 ABC Dec-10 Becky Math 80 70 67 82 299 ABC Dec-10 Becky Science 78 73 66 76 293
ABC Dec-10 Becky History 87 77 79 74 317
ABC DEc-10 Becky English 79 72 84 81 316

I want the output dataset in the form :

Test1
Math July-10 Dec-10
John 90 97
Becky 92 80

I tried using proc tabulate, but could not get the desired form.

Thanks in advance,
Blyzzard

Re: how to change the format of dataset

NickR — Thu, 07 Apr 2011 13:10:11 GMT

data one;
input School $ Month $ StudentName $ Subject $ Test1 Test2 Test3 Test4 Total;
datalines;
ABC July-10 John Math 90 79 86 99 354
ABC July-10 John Science 92 89 99 91 371
ABC July-10 John History 78 69 82 79 308
ABC July-10 John English 81 79 83 82 325
ABC July-10 Becky Math 92 77 76 88 333
ABC July-10 Becky Science 67 76 65 83 291
ABC July-10 Becky History 78 69 76 79 302
ABC July-10 Becky English 90 79 80 81 330
ABC Dec-10 John Math 97 93 89 99 378
ABC Dec-10 John Science 80 89 92 91 352
ABC Dec-10 John History 70 77 76 80 303
ABC Dec-10 John English 80 69 78 81 308
ABC Dec-10 Becky Math 80 70 67 82 299
ABC Dec-10 Becky Science 78 73 66 76 293
ABC Dec-10 Becky History 87 77 79 74 317
ABC Dec-10 Becky English 79 72 84 81 316
;
run;

proc sort data=one; by subject studentname; run;

proc transpose data=one out=two;
id month;
var test1 test2 test3 test4 total;
by subject studentname;
run;

proc print; run;

*partial output;

Subject Name _NAME_ July_10 Dec_10

Math Becky Test1 92 80
Math John Test1 90 97

Re: how to change the format of dataset

SPR — Thu, 07 Apr 2011 13:26:06 GMT

Hello Excelsas,

I do not know how to do it with proc TABULATE but it looks like this code does the trick (I've changed 1test to test1, 2test to test2 to comply with SAS naming requirements):
[pre]
proc sort data=i;
by Subject StudentName;
run;
proc transpose data=i out=r1(drop=_name_) ;
var test1;
id month;
by Subject StudentName;
run;
proc transpose data=i out=r2(drop=_name_) ;
var test2;
id month;
by Subject StudentName;
run;
proc transpose data=i out=r3(drop=_name_) ;
var test3;
id month;
by Subject StudentName;
run;
proc transpose data=i out=r4(drop=_name_) ;
var test4;
id month;
by Subject StudentName;
run;
data combine;
retain test;
set r1(in=r1) r2(in=r2) r3(in=r3) r4(in=r4);
if r1 then test="1test";
if r2 then test="2test";
if r3 then test="3test";
if r4 then test="4test";
run;
[/pre]
Sincerely,
SPR

Re: how to change the format of dataset

AndyJ — Thu, 07 Apr 2011 18:01:25 GMT

Can you try something like this?

data one;
input School $ Month $ StudentName $ Subject $ Test1 Test2 Test3 Test4 Total;
datalines;
ABC July-10 John Math 90 79 86 99 354
ABC July-10 John Science 92 89 99 91 371
ABC July-10 John History 78 69 82 79 308
ABC July-10 John English 81 79 83 82 325
ABC July-10 Becky Math 92 77 76 88 333
ABC July-10 Becky Science 67 76 65 83 291
ABC July-10 Becky History 78 69 76 79 302
ABC July-10 Becky English 90 79 80 81 330
ABC Dec-10 John Math 97 93 89 99 378
ABC Dec-10 John Science 80 89 92 91 352
ABC Dec-10 John History 70 77 76 80 303
ABC Dec-10 John English 80 69 78 81 308
ABC Dec-10 Becky Math 80 70 67 82 299
ABC Dec-10 Becky Science 78 73 66 76 293
ABC Dec-10 Becky History 87 77 79 74 317
ABC Dec-10 Becky English 79 72 84 81 316
;
run;

proc tabulate data=one;
class studentname subject month;
var test1;
table subject, studentname, month*test1;
keylabel sum=' ';

run;

Re: how to change the format of dataset

Ksharp — Fri, 08 Apr 2011 04:16:43 GMT

Emmmm.Since you want dataset like that,It needs some more code,and process is much more complicated.
See whether it is what you want;

[pre]
data one;
input School $ Month $ StudentName $ Subject $ Test1 Test2 Test3 Test4 Total;
datalines;
ABC July-10 John Math 90 79 86 99 354
ABC July-10 John Science 92 89 99 91 371
ABC July-10 John History 78 69 82 79 308
ABC July-10 John English 81 79 83 82 325
ABC July-10 Becky Math 92 77 76 88 333
ABC July-10 Becky Science 67 76 65 83 291
ABC July-10 Becky History 78 69 76 79 302
ABC July-10 Becky English 90 79 80 81 330
ABC Dec-10 John Math 97 93 89 99 378
ABC Dec-10 John Science 80 89 92 91 352
ABC Dec-10 John History 70 77 76 80 303
ABC Dec-10 John English 80 69 78 81 308
ABC Dec-10 Becky Math 80 70 67 82 299
ABC Dec-10 Becky Science 78 73 66 76 293
ABC Dec-10 Becky History 87 77 79 74 317
ABC Dec-10 Becky English 79 72 84 81 316
;
run;

data temp;
set one;
length _test $ 10;
array test{*} test: total;
do i=1 to dim(test);
_test=vname(test{i});
_value=test{i};
output;
end;
drop test: total i;
run;
proc sort data=temp;
by _test subject studentname;
run;
data op;
set temp;
by _test subject studentname;
length _month value $ 50;
retain _month value;
if first.studentname then call missing (_month ,value);
_month=catx(' ',_month,month);
value=catx(' ',value,_value);
if last.studentname then output;
drop _value month ;
run;

data want;
set op;
length row $ 50;
if _test ne lag(_test) then do;row=_test;output; end;
if subject ne lag(subject) then do;row=catx(' ',subject,_month); output; end;
row=catx(' ',studentname,value);output;
keep row;
run;

[/pre]

Ksharp

Message was edited by: Ksharp Message was edited by: Ksharp

Re: how to change the format of dataset

excelsas — Wed, 13 Apr 2011 03:31:31 GMT

Thanks a lot to all. It worked!!