Kristi,

I do not think that proc transpose can handle your problem elegantly. Perhaps it is possible to run many proc transposes in a loop and merge them together, but a better solution would use macros. I tried several approaches (hey-I get to brush up on macro) and this one seemed the clearest. This should work for any data set and variables and the comments should make it somewhat clear what the heck the macro is doing. Good luck.

David W

Code==========================

data ae; input ptid ae $ severe $ medrltd $ aecaus $ onset mmddyy10.; format onset mmddyy10.; cards; 500 cough moderate no no 11/1/2001 500 TIA mild no no 11/5/2001 500 MI severe no no 11/9/2001 ; run; proc sort data=ae out=aesort; by ptid; run;

%macro tranrec (data=,out=&data.tran,by=); /* PROCESS INPUT PARAMETERS */ %let libname=%upcase(%scan(&data,1)); %let memname=%upcase(%scan(&data,2)); %if %length(&memname)=0 %then %do; %let memname=&libname; %let libname=WORK; %end; %let by=%upcase(&by);

** FORM MACRO VARIABLES FOR LATER LOOPING **; proc sql noprint; select nvar-1 into :nvar from dictionary.tables where libname="&libname" and memname="&memname"; %let nvar=&nvar; /* LEFT JUSTIFY */ select name into :var1-:var&nvar from dictionary.columns where libname="&libname" and memname="&memname" and upcase(name)^="&by"; select translate(substr(type,1,1),'$ ','cn')||compress(put(length,8.)) into :len1-:len&nvar from dictionary.columns where libname="&libname" and memname="&memname" and upcase(name)^="&by"; select format into :fmt1-:fmt&nvar from dictionary.columns where libname="&libname" and memname="&memname" and upcase(name)^="&by"; select quote(label) into :lab1-:lab&nvar from dictionary.columns where libname="&libname" and memname="&memname" and upcase(name)^="&by"; quit;

** GET MAX FREQ PER BY VAR - ARRAY DIMENSION **; proc freq data=&data; tables &by / noprint out=freq(keep=&by count); run; data _null_; retain maxcount 0; set freq end=last; if count>maxcount then maxcount=count; if last then call symput('maxcount',compress(maxcount)); run;

** WRITE FINAL DATA SET **; data &out; ** SET ORIGINAL DATA RENAMING VARIABLES SO THERE IS NO CONFLICT W/ARRAYS **; set &data (rename=( %do i = 1 %to &nvar; &&var&i=_&&var&i %end; )); by &by; ** DEFINE NEW VARIABLES **; attrib %do i = 1 %to &nvar; &&var&i..1-&&var&i..&maxcount length=&&len&i %if %length(&&fmt&i)>0 %then format=&&fmt&i; label="&&lab&i" %end; ; ** RETAIN NEW VARS SO THEY CARRY ON TO LAST RECORD PER BY VAR **; retain %do i = 1 %to &nvar; &&var&i..1-&&var&i..&maxcount %end; ; ** DEFINE ARRAYS **; %do i = 1 %to &nvar; array &&var&i(&maxcount); %end; ** INITIALIZE COUNTER AND ARRAYS **; if first.&by then do; counter=0; do i = 1 to &maxcount; %do j = 1 %to &nvar; &&var&j[i]= %if %index(&&len&j,$) %then ''; %else .; ; %end; end; end; counter+1; ** ASSIGN VALUES **; do i = 1 to &maxcount; %do j = 1 %to &nvar; &&var&j[counter]=_&&var&j; %end; end; ** KEEP ONLY ONE REC PER BY VAR **; if last.&by;

** KEEP ONLY THE VARS WE WANT **; keep &by %do i = 1 %to &nvar; &&var&i..1-&&var&i..&maxcount %end; ; run; %mend; options mprint; %tranrec(data=aesort,by=ptid);

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Kristi Mahadocon Sent: Monday, January 07, 2002 12:14 PM To: SAS-L@LISTSERV.UGA.EDU Subject: array vs. transpose

Some time ago, I posted a message about help with proc transpose, and received an answer using arrays that worked quite well for my example. Last week I posted a message about the same problem, but a larger version, and was advised to use proc transpose instead of arrays. I am now quite confused as to the best way to achieve the results I need. I am going to post my original problem and any suggestions on the best solution would be GREATLY appreciated. I am using V6.12 on NT.

I have several datasets such as (simplified version) below:

PTID AE SEVERE MEDRLTD ........ could be up to 17 variables; 500 cough moderate no 500 TIA severe no 500 MI severe no

I need to get them into the form:

PTID AE1 SEVERE1 MEDRLTD1 .... AE2 SEVERE2 MEDRLTD2 500 cough moderate no TIA severe no

The datasets could have 4-17 variables and 2-10 observations per patient and I need to get them back into the one observation per patient format. Obviously I would like a solution that I could put into a macro and call once for each dataset instead of having to write code for each dataset. Am I expecting too much? HELP! TIA, Kristi