topic Need help combining 2 observations on PROC TRANSPOSE in SAS Programming

Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 21:32:29 GMT

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

Re: Need help combining 2 observations on PROC TRANSPOSE

Marina — Mon, 19 Jan 2015 21:57:13 GMT

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.

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:04:12 GMT

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.

Re: Need help combining 2 observations on PROC TRANSPOSE

Reeza — Mon, 19 Jan 2015 22:14:06 GMT

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

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:16:25 GMT

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

Re: Need help combining 2 observations on PROC TRANSPOSE

Reeza — Mon, 19 Jan 2015 22:23:01 GMT

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

Re: Need help combining 2 observations on PROC TRANSPOSE

Giuliano — Mon, 19 Jan 2015 22:27:57 GMT

Ok thank you. I will give that a shot.

Re: Need help combining 2 observations on PROC TRANSPOSE

Marina — Mon, 19 Jan 2015 22:42:24 GMT

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.