SAS date function

sameer112217 — Thu, 10 Jan 2019 13:40:00 GMT

No Type ID Month Amount

2411 Marine Cargo 1001 01APR2015 100
2411 Marine Cargo 1001 01MAY2015 200
2411 Marine Cargo 1001 01JUN2015 300
2411 Marine Cargo 1001 01AUG2015 100
2411 Marine Cargo 1001 01OCT2015
2411 Marine Cargo 1001 01NOV2015 150

2411 Marine Cargo 1001 01DEC2015 50
2411 Marine Cargo 1001 01JAN2016 100
2411 Marine Cargo 1001 01FEB2016 200
2411 Marine Cargo 1001 01MAR2016
2411 Marine Cargo 1001 01APR2016 300
2411 Marine Cargo 1001 01MAY2016 500

2411 Marine Cargo 1001 01JUN2016 20
2411 Marine Cargo 1001 01SEP2016
2411 Marine Cargo 1001 01OCT2016 300
2411 Marine Cargo 1001 01DEC2016

I need the last year month value against the current one. Few sequence in the months column is missing like 01JUL2015, 01SEP2015 in the year 2015 and 01JUL2016, 01JUL2016 and 01NOV2016 , 01JAN2017, 01FEB2017, 01MAR2017 in the year 2016. I need Current year month-last year month ex (01Apr2016-01Apr2015)

I need the below results

No Type ID Month Amount LY_Amount

2411 Marine Cargo 1001 01APR2015 100

2411 Marine Cargo 1001 01MAY2015

2411 Marine Cargo 1001 01JUN2015 200
2411 Marine Cargo 1001 01JUL2015

2411 Marine Cargo 1001 01AUG2015 100

2411 Marine Cargo 1001 01SEP2015
2411 Marine Cargo 1001 01OCT2015
2411 Marine Cargo 1001 01NOV2015 150

2411 Marine Cargo 1001 01JUN2016 20 200

2411 Marine Cargo 1001 01JUL2016 200

2411 Marine Cargo 1001 01AUG2016 100

2411 Marine Cargo 1001 01SEP2016

2411 Marine Cargo 1001 01OCT2016

2411 Marine Cargo 1001 01NOV2016 150

2411 Marine Cargo 1001 01DEC2016 50

2411 Marine Cargo 1001 01JAN2017 100
2411 Marine Cargo 1001 01FEB2017 200

2411 Marine Cargo 1001 01MAR2017

This is just one set of group variables (2411 Marine Cargo 1001) from so many in my entire data. I need the missing months in the column month for that particular and also the group variable values which is 2411, Marine Cargo, 1001 for the same.

I can then try lag function, lag12 for the same or intnx function or some sort of join.

Re: SAS date function

andreas_lds — Thu, 10 Jan 2019 13:59:05 GMT

I understood that you want to add observations for expected but missing month, but what rules are modifying Amount?

Re: SAS date function

sameer112217 — Thu, 10 Jan 2019 16:37:44 GMT

The amount should be zero for missing months. It should have blank values. and the character or group values should be same like below

2411 Marine Cargo 1001

Re: SAS date function

andreas_lds — Fri, 11 Jan 2019 05:53:15 GMT

Fine. And now please explain why you have

No   Type            ID      Month             Amount
2411 Marine Cargo    1001    01MAY2015         200

in the first table, and no value for Amount in the same observation in the second table.

No   Type            ID      Month             Amount
2411 Marine Cargo    1001    01MAY2015

Re: SAS date function

sameer112217 — Fri, 11 Jan 2019 07:03:16 GMT

Please ignore thats typo error. In short I need month comparison from last year based on the same granular grouping.

topic Re: SAS date function in New SAS User

SAS date function

Re: SAS date function

Re: SAS date function

Re: SAS date function

Re: SAS date function