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:
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.