DATA Step, Macro, Functions and more

Need help combining 2 observations on PROC TRANSPOSE

Reply
Occasional Contributor
Posts: 19

Need help combining 2 observations on PROC TRANSPOSE

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;

SOCFeature_CodeDescriptioneffective_dateExtract_DateExctract_Period_Y2015_M02Extract_Period_Y2015_M01
0MGS1OPUTGSTDSharing option8/7/20081/19/2015.20
0MGS1OPUTGSTDSharing option8/17/20081/19/201530.

Message was edited by: Giuliano Filoso

Contributor
Posts: 21

Re: Need help combining 2 observations on PROC TRANSPOSE

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.

Occasional Contributor
Posts: 19

Re: Need help combining 2 observations on PROC TRANSPOSE


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.

Super User
Posts: 17,868

Re: Need help combining 2 observations on PROC TRANSPOSE

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

Occasional Contributor
Posts: 19

Re: Need help combining 2 observations on PROC TRANSPOSE

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

Super User
Posts: 17,868

Re: Need help combining 2 observations on PROC TRANSPOSE

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

Occasional Contributor
Posts: 19

Re: Need help combining 2 observations on PROC TRANSPOSE


Ok thank you.  I will give that a shot.

Contributor
Posts: 21

Re: Need help combining 2 observations on PROC TRANSPOSE

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.

Ask a Question
Discussion stats
  • 7 replies
  • 325 views
  • 3 likes
  • 3 in conversation