Date: Fri, 20 Oct 2000 13:13:37 -0600
Reply-To: Michael Thomas <mjthom4@USWEST.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Michael Thomas <mjthom4@USWEST.COM>
Organization: TRILOGY
Subject: Re: %transpoz - version 1.1
Content-Type: text/plain; charset=us-ascii
Roland,
"You won't want to use proc transpose ever again after using this macro."
Is that a promise??
Michael
Roland wrote:
> I made a slight efficiency after a suggestion by one of the posters
> here. You won't want to use proc transpose ever again after using this
> macro.
>
> Roland
>
> ------------------------------------------------------------------------
> /*
> / PROGRAM NAME: transpoz.sas
> /
> / PROGRAM VERSION: 1.1
> /
> / PROGRAM PURPOSE: To transpose a dataset turning rows into columns.
> /
> / SAS VERSION: 6.12 (Unix)
> /
> / CREATED BY: Roland Rashleigh-Berry
> / DATE: October 2000
> /
> / OUTPUT PRODUCED: None. It will produce an output dataset identified to the
> / out= parameter.
> /
> / NOTES: Using this macro is easier and better than using the
> / "transpose" procedure. It will deal with multiple columns
> / and will maintain the label and format of the column after
> / it has been transposed.
> /
> / You should have sorted your input dataset in "by" variable
> / order plus sorting by any other variables you need to to
> / get the right order of the data. This macro will then
> / maintain that order.
> /
> / PARAMETERS:
> / ----name---- --------------------description-------------------
> / data The input dataset for transposing.
> / out The output dataset after transposing.
> / by The transposing "by" variables.
> / reorder=yes By default reorder the variables at the end to a more
> / logical order.
> / var1-var9 Name of the variable to transpose. These must be single
> / entries.
> / pref1-pref9 Prefix to use as the start of the transposed variable name.
> / You can allow this to default in which case it will use the
> / variable name - truncated if need be.
> /
> / PROGRAM USAGE:
> / -------------
> /
> / data test;
> / id=1001;ptid='NUCB3006';subject=111;output;
> / id=1001;ptid='NUCB3007';subject=112;output;
> / id=1001;ptid='NUCB3008';subject=113;output;
> / id=2001;ptid='NUCB3002';subject=211;output;
> / id=2001;ptid='NUCB3003';subject=212;output;
> / id=2001;ptid='NUCB3004';subject=213;output;
> / format id date7. subject 8.2 ptid $8.;
> / label ptid='Protocol id' subject='Subject number';
> / run;
> /
> / %transpoz(data=test,out=test2,by=id,var1=subject,var2=ptid);
> /
> /==============================================================================
> / CHANGE LOG:
> /
> / MODIFIED BY:
> / DATE:
> / DESCRIPTION:
> /============================================================================*/
>
> %macro transpoz(data=,
> out=,
> by=,
> reorder=yes,
> var1=,pref1=,
> var2=,pref2=,
> var3=,pref3=,
> var4=,pref4=,
> var5=,pref5=,
> var6=,pref6=,
> var7=,pref7=,
> var8=,pref8=,
> var9=,pref9=);
>
> %put ------------------------------------------------------;
> %put NOTE: Macro called: TRANSPOZ.SAS Version Number: 1.1;
> %put ------------------------------------------------------;
>
> %*- Abridge the reorder value to a single upper-case character -;
>
> %if &reorder EQ %then %let reorder=yes;
> %let reorder=%upcase(%substr(&reorder,1,1));
>
> %*- Assign local working macro variables plus macro variables to hold -;
> %*- information about the properties of the variables we wish to transpose -;
>
> %local maxcnt i j dsid
> length1 length2 length3 length4 length5 length6 length7 length8 length9
> label1 label2 label3 label4 label5 label6 label7 label8 label9
> type1 type2 type3 type4 type5 type6 type7 type8 type9
> format1 format2 format3 format4 format5 format6 format7 format8 format9;
>
> %*- Open the input dataset ready to read variable information -;
>
> %let dsid=%sysfunc(open(&data,i));
> %if &dsid EQ 0 %then %do;
> %put %sysfunc(sysmsg());
> %goto error;
> %end;
>
> %*- Loop through the variables declared to the var1-var9 parameters -;
> %*- and write the variable properties to local macro variables -;
>
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
> %let j=%sysfunc(varnum(&dsid,&&var&i));
> %if &j EQ 0 %then %do;
> %put ERROR: Variable &&var&i assigned to VAR&i does not exist in the input dataset.;
> %goto error;
> %end;
> %else %do;
> %let type&i = %sysfunc(vartype(&dsid,&j));
> %let label&i = %sysfunc(varlabel(&dsid,&j));
> %let length&i = %sysfunc(varlength(&dsid,&j));
> %let format&i = %sysfunc(varformat(&dsid,&j));
> %end;
> %end;
> %end;
>
> %*- Close the input dataset -;
>
> %let i=%sysfunc(close(&dsid));
>
> *- Sort the input dataset into the output dataset keeping only the required variables. -;
> *- The existing order will be maintained if the input dataset is already in "by" order -;
>
> proc sort equals data=&data(keep=&by &var1 &var2 &var3 &var4 &var5 &var6 &var7 &var8 &var9)
> out=&out;
> by &by;
> run;
>
> *- When the first of the lowest "by" variable is encountered, _count is set to zero -;
> *- and then incremented after that. We do this to to get the highest value of _count. -;
> *- This gives us the number of variables we will be creating for each existing -;
> *- transpose variable. If _count is a two digit number then the prefix can only be a -;
> *- maximum of 6 characters since variable names are currently limited to 8 characters. -;
>
> data &out;
> retain _count _maxcnt 0;
> set &out end=last;
> by &by;
> if first.%sysfunc(reverse(%scan(%sysfunc(reverse(&by)),1,%str( )))) then _count=0;
> _count=_count+1;
> if last.%sysfunc(reverse(%scan(%sysfunc(reverse(&by)),1,%str( )))) then do;
> if _count > _maxcnt then _maxcnt=_count;
> end;
> if last then call symput('maxcnt',left(put(_maxcnt,6.)));
> drop _maxcnt;
> run;
>
> %*- Test that there really are multiple rows per "by" group -;
>
> %if &maxcnt EQ 1 %then %do;
> %put ERROR: Your input dataset does not have multiple rows per "by" group;
> %put ERROR: so there is no point in transposing the data. Macro aborting.;
> %goto error;
> %end;
>
> %*- Assign defaults to the prefix parameters if missing and then make -;
> %*- sure they do not exceed the maximum length permissable -;
>
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
> %if %length(&&pref&i) EQ 0 %then %let pref&i=&&var&i;
> %if %length(&&pref&i) GT %eval(8-%length(&maxcnt))
> %then %let pref&i=%substr(&&pref&i,1,%eval(8-%length(&maxcnt)));
> %end;
> %end;
>
> *- Create the output dataset with the transposed values in it -;
> data &out;
> attrib
> %*- assign the attributes to all the variables we will create -;
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
>
> &&pref&i..1-&&pref&i..&maxcnt
>
> %if &&type&i EQ C %then %do;
> length=$&&length&i
> %end;
> %else %do;
> length=&&length&i
> %end;
>
> %if %length(&&label&i) GT 0 %then %do;
> label="&&label&i"
> %end;
>
> %if %length(&&format&i) GT 0 %then %do;
> format=&&format&i
> %end;
>
> %end;
> %end;
> ;
> retain
> %*- We need to retain values in the variables we will create -;
> %*- since these will only be output at "by group" end -;
> %do j=1 %to &maxcnt;
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
> &&pref&i..&j
> %end;
> %end;
> %end;
> ;
> set &out;
> by &by;
> *- if the first value of the lowest "by" group variable is encountered -;
> *- then initialise all the new variables to blank or missing values -;
> *- depending on whether the variable is character or numeric -;
> if first.%sysfunc(reverse(%scan(%sysfunc(reverse(&by)),1,%str( )))) then do;
> %do j=1 %to &maxcnt;
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
> %if &&type&i EQ C %then %do;
> &&pref&i..&j=' ';
> %end;
> %else %do;
> &&pref&i..&j=.;
> %end;
> %end;
> %end;
> %end;
> end;
> *- dummy statement so that following -;
> *- conditions start with "else" -;
> if _count EQ 0 then do;
> end;
> *- If _count equals 1 then the "1" variables are set equal to the -;
> *- variables we are transposing. If _count equals 2 then the "2" -;
> *- variables are set equal to the variables we are transposing etc. -;
> %do i=1 %to &maxcnt;
> else if _count EQ &i then do;
> %do j=1 %to 9;
> %if %length(&&var&j) GT 0 %then %do;
> &&pref&j..&i = &&var&j;
> %end;
> %end;
> end;
> %end;
> *- If the last value of the lowest "by" group variable -;
> *- is encountered then output the observation. -;
> if last.%sysfunc(reverse(%scan(%sysfunc(reverse(&by)),1,%str( )))) then output;
> *- drop the _count variable and the original variables we are tramsposing -;
> drop _count &var1 &var2 &var3 &var4 &var5 &var6 &var7 &var8 &var9;
> run;
>
> %if &reorder EQ Y %then %do;
>
> *- Reorder the variables more logically if the option is set -;
> data &out;
> retain &by
> %do j=1 %to &maxcnt;
> %do i=1 %to 9;
> %if %length(&&var&i) GT 0 %then %do;
> &&pref&i..&j
> %end;
> %end;
> %end;
> ;
> set &out;
> run;
> %end;
>
> %goto exit;
> %error:
> %put >>>>>>>>>>>>>>>>> quitting TRANSPOZ macro due to error just listed >>>>>>>>>>>>>>>>>;
> %exit:
> %mend;
| 
