Need help combining 2 observations on PROC TRANSPOSE

[Giuliano]

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

[Marina]

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.

[Giuliano]

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.

[Reeza]

If you need more control over your transpose use a data step instead.

[Giuliano]

And just forget about the Transpose all together?  Do a Merge maybe?

[Reeza]

A merge would work, I would use SQL over data step though.

[Giuliano]

Ok thank you.  I will give that a shot.

[Marina]

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.