01-19-2015 04:32 PM
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;
PROC TRANSPOSE DATA = rashare.gf_temp_mobility_RC_jan19
out=work.longf (DROP = _name_ _Label_)
PREFIX = EXTRACT_PERIOD_
BY SOC Feature_Code soc_description effective_date expiration_date Extract_Date;
Message was edited by: Giuliano Filoso
01-19-2015 04:57 PM
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.
01-19-2015 05:04 PM
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.
01-19-2015 05:42 PM
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.