topic Re: Trouble renaming variable after transposing in SAS Procedures

Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 18:24:13 GMT

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;

Re: Trouble renaming variable after transposing

PaigeMiller — Mon, 23 Mar 2020 18:27:01 GMT

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).

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 18:36:53 GMT

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

Re: Trouble renaming variable after transposing

PaigeMiller — Mon, 23 Mar 2020 18:53:01 GMT

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.

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 18:53:02 GMT

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.

Re: Trouble renaming variable after transposing

PaigeMiller — Mon, 23 Mar 2020 18:53:36 GMT

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

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 18:55:37 GMT

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)

Re: Trouble renaming variable after transposing

Tom — Mon, 23 Mar 2020 19:01:29 GMT

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.

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 19:11:44 GMT

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?

Re: Trouble renaming variable after transposing

PaigeMiller — Mon, 23 Mar 2020 19:13:25 GMT

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

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 19:14:11 GMT

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

Re: Trouble renaming variable after transposing

Tom — Mon, 23 Mar 2020 19:15:05 GMT

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

Re: Trouble renaming variable after transposing

monsterpie — Mon, 23 Mar 2020 19:21:38 GMT

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;

Re: Trouble renaming variable after transposing

Tom — Mon, 23 Mar 2020 19:28:18 GMT

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.

Re: Trouble renaming variable after transposing

Reeza — Mon, 23 Mar 2020 19:35:15 GMT

@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;

Re: Trouble renaming variable after transposing

Reeza — Mon, 23 Mar 2020 19:35:52 GMT

@Tom wrote:

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.

Agreed.

Re: Trouble renaming variable after transposing

PaigeMiller — Mon, 23 Mar 2020 19:38:21 GMT

The log provided explains why the variable was not renamed via PROC SORT

Re: Trouble renaming variable after transposing

Reeza — Mon, 23 Mar 2020 19:39:45 GMT

It doesn't indicate any issue with renaming as far as I can see, it says the data set is not sorted but it also says the second data set is created and that shows the rename correctly. I could be missing something but I think both Tom and my example show the code is valid and correct.

Re: Trouble renaming variable after transposing

Reeza — Mon, 23 Mar 2020 19:40:36 GMT

@monsterpie wrote:
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;

Show the log.

Re: Trouble renaming variable after transposing

PaigeMiller — Tue, 24 Mar 2020 10:36:52 GMT

@Reeza wrote:
It doesn't indicate any issue with renaming as far as I can see, it says the data set is not sorted but it also says the second data set is created and that shows the rename correctly. I could be missing something but I think both Tom and my example show the code is valid and correct.

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

When you make a copy, the variable names are not changed.