Hi,
I am using a PROC APPEND to gather my data monthly. I am then using PROC TRANSPOSE to change the format of my data. Each month I will have a new column added to the end of my spreadsheet to compare pricing month to month. I ran a test to compare what happens when a price changes from one month to the next. Everything looks good except I when there was a pricing change from one month to the next it created a new line. I just want one line with the price from month to month. I pasted below to show you what it looks like now along with the last part of my code. I just need everything on one line, not two. Not sure if I need to modify my TRANPOSE or insert a new DATA STEP afterwards to combine the 2. I still want the old rate to appear in last month columns and I still want the new rate to compare in this months column. Everything else with the program works perfectly. Any suggestions?
PROC SORT data=work.mobpp4;
BY soc DESCENDING effective_date;
RUN;
PROC TRANSPOSE DATA = rashare.gf_temp_mobility_RC_jan19
out=work.longf (DROP = _name_ _Label_)
PREFIX = EXTRACT_PERIOD_
LET;
BY SOC Feature_Code soc_description effective_date expiration_date Extract_Date;
ID Extract_Year_Month;
VAR RATE;
RUN;
SOC | Feature_Code | Description | effective_date | Extract_Date | Exctract_Period_Y2015_M02 | Extract_Period_Y2015_M01 |
0MGS1OP | UTGSTD | Sharing option | 8/7/2008 | 1/19/2015 | . | 20 |
0MGS1OP | UTGSTD | Sharing option | 8/17/2008 | 1/19/2015 | 30 | . |
Message was edited by: Giuliano Filoso
The reason you have two lines, is the different effective_date values. In order to have only one line per group of BY variables, all values should be equal. You can move effective_date from BY statement to VAR statement.
I know but if I move the effective_date to a VAR statement then it throws everything else off. I have to somehow keep this exact format and join my 2 obervations in 1 line.
If you need more control over your transpose use a data step instead.
And just forget about the Transpose all together? Do a Merge maybe?
A merge would work, I would use SQL over data step though.
Ok thank you. I will give that a shot.
It does not matter what you use, proc transpose or merge, as long as you have different effective dates, you will end up either with multiple observations or with multiple effective_date columns (if you rename it in one of the contributing datasets - the same as if you are to move it in VAR statement in transpose). If you know what date you need to keep as effective_date, e.g. earliest or latest or whatever, you could add data step to collapse multiple observations.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.