BookmarkSubscribeRSS Feed
SasDewd
Obsidian | Level 7

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!

3 REPLIES 3
Astounding
PROC Star

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.

Reeza
Super User

@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. 

SasDewd
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 735 views
  • 0 likes
  • 3 in conversation