Trouble renaming variable after transposing

[monsterpie]

Hi, 

I have a dataset that I transposed, however, I am trying to rename an id variable (ptid) before I merge this dataset with another one. I don't get an error message but after running the rename statement, it does not change the variable in the dataset (and when I run a proc contents to see the variable name, it says the variable (and label) are ptid). Any help would be greatly appreciated!

This is the code I ran to transpose the dataset (would this have cause any issues with my renaming): 

data heartcount;
set newresults;
array diag (24) diag1-diag24;
heart=0;
do _n_=1 to dim(diag);
if diag{_n_}in: ("250","E11","E10") then heart+1;
end;
run;

trying to rename variable and sort dataset before I merge:

proc sort data = heartcount out=ribdata (rename= ptid=idnum);
by ptid;
run;

[PaigeMiller]

You don't get an error message, but the code you show absolutely will produce an error message.

The proper syntax is

proc sort data = ex.spine out=spine2 (rename=(ptid=idnum));
by ptid;
run;

Note the extra parentheses.

If that doesn't work for you, please show us the entire log for these steps (code as seen in the log, plus any errors or warnings, do not chop out any part of the log for these steps).

[monsterpie]

Hi Paige, this is the log I get for when I try the correct syntax you provided:

364 proc sort data = heartcount out= spine2 (rename=(ptid=idnum));
365 by ptid;
366 run;

NOTE: Input data set is already sorted; it has been copied to the output data set.
NOTE: There were 32844 observations read from the data set WORK.HEARTCOUNT.
NOTE: The data set WORK.SPINE2 has 32844 observations and 28 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

[PaigeMiller]

If you read the log, it explains why no sorting was done, and why the variable is not renamed.

NOTE: Input data set is already sorted; it has been copied to the output data set.

[monsterpie]

yes, the issue is that in spine2 (or even if I just try to rename it in the original dataset - heartcount) it does not get renamed.

[PaigeMiller]

You'll have to rename it in whatever the next step is.

[monsterpie]

well my next step is to merge spine2 with another dataset. But I thought that you had to rename id variables to match before merging two datasets? Hence why I was trying to rename pt id to idnum (which is in the other dataset that I will be merging with)

[Reeza]

@PaigeMiller @Tom You can definitely rename within a sort and you do not need brackets around the rename if it's only one set of variables so that's not the issue with OP's code, something else is the issue. This code works perfectly fine and the class2 data set has the renamed variable as indicated.

data class;
set sashelp.class;
run;

proc sort data=class;
by sex age;
run;

proc sort data=class out=class2(rename=name=ID);
by sex age;
run;

proc contents data=class2;
run;

[Tom]

Are you sure you aren't looking at the LABEL instead of the NAME of the variable.

It definitely works for me, even when the data is sorted already.

2897  proc sort data=sashelp.class out=class ; by sex; run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds


2898  proc sort data=class(rename=(name=name2)) out=class2; by sex; run;

NOTE: Input data set is already sorted; it has been copied to the output data set.
NOTE: There were 19 observations read from the data set WORK.CLASS.
NOTE: The data set WORK.CLASS2 has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


2899  proc compare data=class compare=class2; run;

Results

Data Set Summary

Dataset               Created          Modified  NVar    NObs  Label

WORK.CLASS   23MAR20:14:56:21  23MAR20:14:56:21     5      19  Student Data
WORK.CLASS2  23MAR20:14:56:21  23MAR20:14:56:21     5      19  Student Data


Variables Summary

Number of Variables in Common: 4.
Number of Variables in WORK.CLASS but not in WORK.CLASS2: 1.
Number of Variables in WORK.CLASS2 but not in WORK.CLASS: 1.

SAS 9.4 on WINDOWS                                                       13:12 Thursday, March 19, 2020  44

The COMPARE Procedure
Comparison of WORK.CLASS with WORK.CLASS2
(Method=EXACT)

Observation Summary

Observation      Base  Compare

First Obs           1        1
Last  Obs          19       19

Number of Observations in Common: 19.
Total Number of Observations Read from WORK.CLASS: 19.
Total Number of Observations Read from WORK.CLASS2: 19.

Number of Observations with Some Compared Variables Unequal: 0.
Number of Observations with All Compared Variables Equal: 19.

NOTE: No unequal values were found. All values compared are exactly equal.

[monsterpie]

I just ran the code again and it still doesn't seem to work...but I am going through all my steps again, and I'm just wondering if there is a way to rename ptid in the proc transpose statement?

[PaigeMiller]

Yes you can rename it during PROC TRANSPOSE, or you can rename in the step where you do the merge.

[monsterpie]

would you be able to give me an example of either of those syntaxes please?

[Tom · Accepted solution]

Yes. The OUT= option of PROC TRANSPOSE is a dataset reference. So you can include dataset options. Which include the RENAME= option.

[monsterpie]

ok,so I ran this on a different set of data but even in this data the variable is not renamed in the new dataset.
Proc transpose data=shortdiag
out=newresults2 (rename=(hdghraencwid=hraencwid))
prefix=diag;
by hdgHraEncWid;
var diagnosis;
run;

[Tom]

I really doubt that RENAME= option is not working for you.  You are either looking at the wrong dataset or not looking at the NAME of the variable.  

Please show the proc contents for the dataset newresults2.