PROC SQL rename variable when creating a table

[jeremy4]

Hi,

I have the following code below but can someone help me to rename the 'stage' and 'balance' variables (highlighted in red) to something like 'stage_March_2018' and 'balance_March_2018' in dataset B and 'stage_March_2019' and 'balance_March_2019' in dataset A because my current code output only shows for March 2019, whereas I would like the table to show the 'stage' and 'balance' for both March 2018 and March 2019 to see how it has changed? Thanks.

Currently, the output based on my code only shows the relevant data for account_ID, stage (from the 2019 dataset), balance (from the 2019 dataset) and time_since_update. Is there a way to change the code to rename 'stage' and 'balance' from the 2018 dataset and have these two renamed columns appear in the output table? I would like the output table to look something like the example below:

Code:

data MARCH_2018;
   set cdp.v_2018;
run;

data MARCH_2019;
   set cdp.v_2019;
run;

proc sql;
   create table accounts_that_changed as
   select a.account_ID, a.stage, a.balance, a.time_since_update, b.stage, b.balance
   from MARCH_2018 as a
   inner join MARCH_2019 as b
   on a.account_ID=b.account_ID
order by a.account_ID
;
quit;

[Reeza]

---

@jeremy4 wrote:

Hi,

I have the following code below but can someone help me to rename the 'stage' and 'balance' variables (highlighted in red) to something like 'stage_March_2018' and 'balance_March_2018' in dataset B and 'stage_March_2019' and 'balance_March_2019' in dataset A because my current code output only shows for March 2019, whereas I would like the table to show the 'stage' and 'balance' for both March 2018 and March 2019 to see how it has changed? Thanks.

Currently, the output based on my code only shows the relevant data for account_ID, stage (from the 2019 dataset), balance (from the 2019 dataset) and time_since_update. Is there a way to change the code to rename 'stage' and 'balance' from the 2018 dataset and have these two renamed columns appear in the output table? I would like the output table to look something like the example below:

Code:

data MARCH_2018;
   set cdp.v_2018;
run;

data MARCH_2019;
   set cdp.v_2019;
run;

proc sql;
   create table accounts_that_changed as
   select a.account_ID, a.stage, a.balance, a.time_since_update, b.stage, b.balance
   from MARCH_2018 as a
   inner join MARCH_2019 as b
   on a.account_ID=b.account_ID
order by a.account_ID
;
quit;

---

proc sql;
   create table accounts_that_changed as
   select a.account_ID, a.stage as STAGE_MARCH_2019, a.balance as BALANCE_MARCH_2019, 
   a.time_since_update, b.stage, b.balance
   from MARCH_2018 as a
   inner join MARCH_2019 as b
   on a.account_ID=b.account_ID
order by a.account_ID
;
quit;

Rename variables using AS and then the new name after.

[ballardw · Accepted solution]

More than half the time having a wide format with variable names containing actual data, i.e. March, is a poor idea.

The SQL syntax is to use: value as variablename. The value could be a literal value such as 'ABC' or an existing variable or a calculation: a.stage*a.balance as stagebalance.

proc sql;
   create table accounts_that_changed as
   select a.account_ID, a.stage AS NEWSTAGE, a.balance AS NEWBALANCE, a.time_since_update, b.stage, b.balance
   from MARCH_2018 as a
   inner join MARCH_2019 as b
   on a.account_ID=b.account_ID
order by a.account_ID
;
quit;

[Astounding]

Also note, in the full program you presented, you can uncomplicate things earlier:

data MARCH_2018;
   set cdp.v_2018;

   rename stage = stage_March_2018;

   rename balance = balance_March_2018;
run;

data MARCH_2019;
   set cdp.v_2019;

   rename stage = stage_March_2019;

   rename balance = balance_March_2019;

run;