topic Re: Adding a computed column to a data set based on the last 2 columns that are dynamically named, in SAS Programming

Adding a computed column to a data set based on the last 2 columns that are dynamically named,

SasDewd — Tue, 03 Apr 2018 19:19:02 GMT

Hello,

I'm new to SAS and this may or may not be simple but I spent a couple of days and cannot figure it out.

I have a data set that I got through PROC SQL and TRANSPOSE and some other data manipulation procedures.

It has several columns that depend on the date the project is run on.

For example, if run today it'll have columns named 31Mar2018, 01APR2018, and 02APR2018 (let's say-it'll have more than 3 columns but this is the idea).

I need to add another column that will look at the last 2 columns 1st and 2nd APR and compare the values and display the change (for example 27% as in 27% increase in sales).

Is this possible to do?

Thanks!

Re: Adding a computed column to a data set based on the last 2 columns that are dynamically named,

Astounding — Tue, 03 Apr 2018 19:59:18 GMT

You created this nightmare by transposing the data. The previous form was an excellent format for programming. Just go back to the data at that point, and program with the untransposed data. The only reason to transpose the data would be if the final reporting procedure you are using doesn't know how to do that on its own. Otherwise, it should never be transposed in that way.

Re: Adding a computed column to a data set based on the last 2 columns that are dynamically named,

Reeza — Tue, 03 Apr 2018 20:09:51 GMT

@SasDewd wrote:

Hello,

Is this possible to do?

Thanks!

Yes it is.

Did you need to know how? These are the steps I'd take:

1. Add a prefix to your Transpose so your columns have a standard prefix and you can short cut reference your variable list

2. Change your dates so that you can be assured they'll be in the correct order. I don't think you can with Date9 format, try YYMMDD for example.

3. In a data step create an array, using the PREFIX to list the variables

4. Use DIM() to get the number of items. Now you have the dimension and can easily reference the last and second last.

Since you mention computed column, I'm going to assume you're using EG. AFAIK there isn't an easy way to do this via the GUI but it's relatively easy to program.

Good Luck.

Re: Adding a computed column to a data set based on the last 2 columns that are dynamically named,

SasDewd — Tue, 03 Apr 2018 20:55:41 GMT

Thank you @Astounding and @Reeza !

You might be right, Astounding, transpose did complicate it but I need to replicate an existing report and so I don't have much say in how it's displayed. Also, that last column was requested after I thought I had everything done, the way I wanted it.

I still think the biggest problem is the names of columns that are dynamically assigned so cannot be hardcoded.

I'll try to do it the way you suggested, Reeza.

It makes perfect sense but, like I said, I'm new to SAS so hopefully I can figure it out.

I completed the first 2 steps already so now I just need to figure out the other 2 - the array and logic to create a new, computed, column.

Thanks again!