BookmarkSubscribeRSS Feed
Giuliano
Fluorite | Level 6

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

7 REPLIES 7
Marina
Calcite | Level 5

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
Fluorite | Level 6


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
Super User

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

Giuliano
Fluorite | Level 6

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

Reeza
Super User

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

Giuliano
Fluorite | Level 6


Ok thank you.  I will give that a shot.

Marina
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1125 views
  • 3 likes
  • 3 in conversation