Creating a set of features based off of a one date column: one-hot-encoding without blanks

[sassing]

Hi SAS Community, 

I want to create a set of features based on a date index- that is, a set of features based on a date on one line per ID key. 

This is the data:

What I want it to look like: 

This is the code I came up with:

data x;
set x;

if date_field = '30SEP2018:00:00:00.000'dt then SEP2018_BAL= amount;
else if date_field = '31OCT2018:00:00:00.000'dt then OCT2018_BAL= amount;
else if date_field = '30NOV2018:00:00:00.000'dt then NOV2018_BAL= amount;
else if date_field = '31DEC2018:00:00:00.000'dt then DEC2018_BAL= amount;
else if date_field = '31JAN2019:00:00:00.000'dt then JAN2019_BAL= amount;
else if date_field = '28FEB2019:00:00:00.000'dt then FEB2019_BAL= amount;


run;

And this is the output I am getting, which is not ideal:

Let me know if you need any more info from me. Thank you! 

[PeterClemmensen · Accepted solution]

Hi @sassing and welcome to the SAS Community 🙂

Here's how I would do it

data have;
retain ID_key "a1_1234";
input amount date_field :anydtdtm22.;
format date_field datetime22.;
datalines;
710937.5  30SEP2018:00:00:00.000
713653.38 31OCT2018:00:00:00.000
705974.23 30NOV2018:00:00:00.000
704847.1  31DEC2018:00:00:00.000
704016.17 31JAN2019:00:00:00.000
703178.11 28FEB2019:00:00:00.000
;

data temp;
    set have;
    vn=cats(put(datepart(date_field), monyy7.), '_BAL');
run;

proc transpose data=temp out=want(drop=_:);
    by ID_key;
    id vn;
    var amount;
run;

[sassing]

@PeterClemmensen, THANK YOU SO MUCH! THIS WORKS! 

Have a good day further.

[PeterClemmensen]

Anytime 🙂 You too