Write non-existent variables in a PROC TRANSPOSE statement

[mvalsamis]

Hi to the community! I am having the following issue. I am having a dataset of banking transactions by customer and month. However, currently (July 2025) there have been transactions only in month1 (April 2025). See below a simplified example:

id	month	amount
1	month1	23
1	month1	12
2	month1	90
2	month1	4

I want to transpose using PROC TRANSPOSE as follows:

id	month1	month2	month3
1	23	.	.
1	12	.	.
2	90	.	.
2	4	.	.

To do so, I am using the following PROC TRANSPOSE statement:

proc transpose data=amounts out=amounts_trans (keep=id month1 month2 month3);
  by id;
  var amount;
  id Month;
run;

However, because I do not have data (at least this month) for month2 and month3, this statement results in the following table:

id	month1
1	23
1	12
2	90
2	4

And it gives me those warnings:

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

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

I need variables month2 and month3 in the transposed table, because they are referenced later in the script. Do you know how I can solve this issue? I could use DATA step and arrays instead of PROC TRANSPOSE, but I am not allowed, as I have to keep the code as original as possible.

[Ksharp · Accepted solution]

You need to make a dummy dataset to include all these months and using MERGE skill to get job done.

data have;
input id month $ amount;
cards;
1 month1 23
1 month1 12
1 month2 10
2 month1 90
2 month1 4
;




*Make a dummy MONTH dataset;
data month;
input month $;
cards;
month1
month2
month3
;
proc sql noprint;
select distinct catt('have(where=(month="',month,'") rename=(amount=',month,'))') into :merge separated by ' ' 
 from month;
quit;
data want;
 merge &merge.;
 by id;
 output;
 call missing(of _all_);
 drop month;
run;