Using prefix before a variable in proc transpose

[georgel]

Dear Users, 

I would like to have a prefix before each variable is created instead of _1, _2, _3

when I run the following: (actually BHARced_1 BHARced_2 BHARced_3 and then for the second BHRced_1..

options mprint;
*move the quarters as columns;
%let varlist = BHARced BHRced;

%macro rename;
   %let word_cnt=%sysfunc(countw(&varlist)); 
   %do i = 1 %to &word_cnt; 
     %let temp=%qscan(%bquote(&varlist),&i);
	    proc transpose data=dsf4(keep=cusip year_performance &temp )
					out=performance_&i
					name= &temp;
					id year_performance;
	                *prefix= &name;
					by cusip ;
					
	    run;
        data performance_&i(rename=(_1= 1_&temp _2= 2_&temp _3=3_&temp));/*there is a proble, here*/
		     set performance_&i;
		run;

   %end;
%mend rename;
%rename;

Log output:

MPRINT(RENAME):   proc transpose data=dsf4(keep=cusip year_performance BHARced )
out=performance_1 name= BHARced;
MPRINT(RENAME):   id year_performance;
MPRINT(RENAME):   *prefix= &name;
MPRINT(RENAME):   by cusip ;
MPRINT(RENAME):   run;

NOTE: There were 3301 observations read from the data set WORK.DSF4.
NOTE: The data set WORK.PERFORMANCE_1 has 1186 observations and 5 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


214: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
      where the error has occurred.
ERROR 214-322: Variable name 1 is not valid.
NOTE 138-205: Line generated by the macro variable "TEMP".
1     _BHARced
       -------
       79
ERROR 79-322: Expecting a =.

23: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and
              COLUMN where the error has occurred.
ERROR 23-7: Invalid value for the RENAME option.
MPRINT(RENAME):   data performance_1(rename=(_1= 1_BHARced _2= 2_BHARced _3=3_BHARced));
MPRINT(RENAME):   set performance_1;
MPRINT(RENAME):   run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(RENAME):   proc transpose data=dsf4(keep=cusip year_performance BHRced ) out=performance_2
name= BHRced;
MPRINT(RENAME):   id year_performance;
MPRINT(RENAME):   *prefix= &name;
MPRINT(RENAME):   by cusip ;
MPRINT(RENAME):   run;

NOTE: There were 3301 observations read from the data set WORK.DSF4.
NOTE: The data set WORK.PERFORMANCE_2 has 1186 observations and 5 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


214: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
      where the error has occurred.
ERROR 214-322: Variable name 1 is not valid.
NOTE 138-205: Line generated by the macro variable "TEMP".
1     _BHRced
       ------
       79
ERROR 79-322: Expecting a =.

23: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and
              COLUMN where the error has occurred.
ERROR 23-7: Invalid value for the RENAME option.
MPRINT(RENAME):   data performance_2(rename=(_1= 1_BHRced _2= 2_BHRced _3=3_BHRced));
MPRINT(RENAME):   set performance_2;
MPRINT(RENAME):   run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Thank you very much PaigeMiller for your great guidelines

[PaigeMiller]

/*there is a proble, here*/

Whenever code isn't working right, we normally ask to see the LOG. We need to see the ENTIRE log for this macro, every single line, every single character; do not pick and choose parts of the log to show us. Copy the log as text and paste it into the window that appears when you click on the </> icon.

In addition, since it is a macro problem, please run this line and then run your code again and show us that log.

options mprint;

From now on, we expect you to show us the LOG every single time you have an error or problem.

[Kurt_Bremser · Accepted solution]

PREFIX= is an option of the PROC TRANSPOSE statement.

[ballardw]

Prefix option goes into the PROC statement.

This example will PREFIX the name of the variable with ABC before the value of the ID variable.

proc transpose data=sashelp.class(obs=5)
     out =trans prefix=ABC;
   by name;
   var age;
   id sex;
run;

The obs limit is just to make a very small set easy to read.

[Tom]

I suspect that you would be a lot better served to use PROC SUMMARY and the IDGROUP concept to transpose your data since this will allow you to transpose multiple variables at once.

https://support.sas.com/resources/papers/proceedings10/102-2010.pdf