Unable to Rename - Proc Transpose; SAS on Demand

[alphangadan]

Dear Reeza or any other senior members,

I have been trying to solve this on my own but I am stuck. 

I have been using SAS Studio UE since 2015 and I have no issue running this proc transpose and rename statement. But since I migrate to SAS on Demand, and running the same code, I received this error.

WARNING: The variable _1 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable _2 in the DROP, KEEP, or RENAME list has never been referenced.

My codes;

Proc means data=msia.mewretdat mean t probt;
 class momr;
 var mewret;
run;

/* Calculate Buy-Sell Portfolio Returns */
Proc sort data=msia.mewretdat; by date momr; run;
proc transpose data=msia.mewretdat out=msia.mewretdat1 (rename=(_1=Sell _2=BUY));
 by date;
 id momr;
 var mewret;

data msia.mewretdat2;
set msia.mewretdat1;
BUY_SELL=BuY-Sell;
Run;

Proc means data=msia.mewretdat2 n mean t probt;
 var Sell Buy Buy_Sell;
Run;

Appreciate the guidance.

Best regards,

Alpha

[PaigeMiller]

proc transpose data=msia.mewretdat out=msia.mewretdat1 (rename=(_1=Sell _2=BUY));

You can't rename variable named _1 because it is not in SAS data set msia.mewretdat1

You can't rename variable named _2 because it is not in SAS data set msia.mewretdat1

Please look at a PROC CONTENTS of data set msia.mewretdat1

From now on (even before someone asks), when you have errors in the LOG, show us the ENTIRE log (not just selected parts) so we can see the CODE as it appears in the log, the NOTEs, WARNINGs and ERRORs.

[alphangadan]

Dear PaigeMiller,

Thanks for the reply. Please find the attached proc contents and log files. As mentioned earlier, I have been running these codes on the University Edition and it works fine. The error only shows when I run the codes on SAS on Demand. I am also attaching the log files for the successful rename using  IE web browser University Edition.

Thanks,

Alpha

[Tom]

So your first PDF file has the output of PROC CONTENTS showing that the variables are named '1'n and '2'n, not _1 and _2 like your code is using.

Please compare the value of the VALIDVARNAME option between the two systems.

If you name a variable from the value of a variable (by using the ID statement in PROC TRANSPOSE) and you don't have VALIDVARNAME=ANY then PROC TRANSPOSE will prefix an underscore before values like 1 or '1' because under normal SAS naming rules a name cannot start with a digit.  But with VALIDVARNAME=ANY such names are allowed so PROC TRANSPOSE will create them.   

Personally I never use VALIDVARNAME=ANY unless I really have to and even then I only keep it set that way until I can get the variables renamed to valid SAS variable names.

[alphangadan]

Thanks Tom for the reply.

As mentioned in my earlier post, I have never encountered this error when working on the same codes with UE. I have migrated my files to SAS on Demand, and that particular part of my code is not working. Since I have never used VALIDVARNAME=ANY on the UE, I will have to study and look for examples of applying that to my existing codes.

To add, the 1 and 2 are rankings - MOMR where I group the portfolio. To calculate the Buy minus Sell, I need 1 to be Sell and 2 to be Buy. I have attached the results from the UE. 

[Kurt_Bremser · Accepted solution]

VALIDVARNAME can be set from a user setting in SAS Studio; I take it that your UE Studio was set to use V7, while On Demand you have the default setting of ANY.

PS It is a setting in the Tables or Datasets section of the user settings.

[alphangadan]

Thanks, Kurt,

It works well, and I can generate the same results as UE.

Appreciate your comments.

Best regards,
Alpha

[PaigeMiller]

---

@alphangadan wrote:

Dear PaigeMiller,

Thanks for the reply. Please find the attached proc contents and log files. As mentioned earlier, I have been running these codes on the University Edition and it works fine. The error only shows when I run the codes on SAS on Demand. I am also attaching the log files for the successful rename using  IE web browser University Edition.

---

The idea that something worked in UE so it should work the same under SAS on Demand is the problem here. Clearly, something is different, and the fact that it worked in UE is irrelevant.

As pointed out by others, if an option is changed, then something that worked under UE will not work under SAS on Demand.

Thank you for providing the PROC CONTENTS. However, many of us will not download attachments (or our employer does not allow it), and so in the future please include output (such as PROC CONTENTS output) in your reply as text.