On Tue, 16 Dec 2008 17:25:56 -0600, Mary <mlhoward@AVALON.NET> wrote:

>Here's a "Wide" solution; your question below has 6 months so I did it for >that; >I didn't see the 24 months extension of the question until after I was >finished. > >Again, you need to decide how to store the data based on what you are going >to do with it; only store it wide if you are going to use it wide, but store >it >long if you are going to use it long. > >-Mary > >%let start_month=2; >data master; >infile cards missover; >input >Userid trans_id sales_code dec_sales nov_sales sept_sales dec_returns >nov_returns sept_returns; >cards; >1 100 10 5 4 5 0 2 1 >2 101 10 1 1 1 0 0 0 >3 102 11 9 9 9 8 5 3 >1 100 11 12 10 19 0 0 7 >run; > >data monthly; >infile cards missover; >input >Userid trans_id sales_code jan_sales jan_returns; >cards; >1 100 10 50 10 >2 101 10 10 5 >4 105 12 5 2 >; >run; >proc format; >value monthfmt 1='jan' >2='feb' >3='mar' >4='apr' >5='may' >6='jun' >7='jul' >8='aug' >9='sep' >10='oct' >11='nov' >12='dec'; >run; > >data months; >informat month $3.; >/* start_month=month( today() ); */ >start_month=&start_month; >month1=start_month-1; >month2=start_month-2; >month3=start_month-3; >month4=start_month-4; >month5=start_month-5; >month6=start_month-6; >array month_array{*} month1--month6; >do i=1 to dim(month_array); > if month_array[i]=0 then month_array[i]=12; > else if month_array[i]=-1 then month_array[i]=11; > else if month_array[i]=-2 then month_array[i]=10; > else if month_array[i]=-3 then month_array[i]=9; > else if month_array[i]=-4 then month_array[i]=8; > else if month_array[i]=-5 then month_array[i]=7; > month=put(month_array[i],monthfmt.); >output; >end; >keep month; >run; > >proc sql noprint; >select trim(month) || '_sales' into :salesmonths separated by ' ' >from months; >select trim(month) || '_returns' into :returnmonths separated by ' ' >from months; >quit; >%put sales months=&salesmonths; >%put return months=&returnmonths; > >proc sort data=master; >by userid trans_id sales_code; >run; > >proc sort data=monthly; >by userid trans_id sales_code; >run; > >data newmaster; >merge master monthly; >by userid trans_id sales_code; >keep userid trans_id sales_code &salesmonths &returnmonths; >run;

This does not place the variables in the specified order. That may be critical. I suspect the analysts are using double-hyphen variable lists, as in

var sales_jan--sales_aug;

A RETAIN statement before the MERGE statement should take care of that.

Here is a DATA step to replace the PROC FORMAT step and the following DATA and PROC SQL steps:

data _null_; start_month = input("&start_month",monyy7.); length monthstring $ 999; do monthindex = 1 to 24; monthstring = catx( ' ' , monthstring , cat( '_' , put( intnx( 'mon' , start_month ,-(monthindex) ) , monyy7. ) ) ); end; call symput('monthstring',monthstring); run;

%let salesmonths = %sysfunc( tranwrd(&monthstring,_, Sales_) ); %let returnsmonths = %sysfunc( tranwrd(&monthstring,_,Returns_) );

This also takes care of making the variable names explicitly year-specific.

> > >----- Original Message ----- >From: sasbeginner >To: SAS-L@LISTSERV.UGA.EDU >Sent: Friday, December 12, 2008 3:10 PM >Subject: Compare and read values into master data table > > >Hi, > >Am trying to work around with a small data set. As I am a beginner I >do not know how to handle this situatiopn. > >I have two files. The first three variables has unique values. First >file is the Masterframe file and the second one is something that I >would be getting on month by month basis. Before reading the new data >i would add a column to before each of the variable. In this case >Sales and returns are two variables in my file. Then if the 3 key >variables are present then I would read that value and enter in Jan >data. My 6 th month data would be flushed out. If the key variables do >not match then I would simply add that row and make other month's >values zero and retain just this month data. > >How do I achieve this. Should I use outer joins??? Any good work >around to do it in a simpler way!!! Geez Christmas time and this data >pulling rolling my head off:( > > >Any small help is appreciated and thanks much for anyone who is trying >to help me out. > > > >Userid trans_id sales_code Dec_sales Nov_sales >sept_sales dec_returns >nov_returns sept_returns > >1 100 10 5 4 5 0 2 1 >2 101 10 1 1 1 0 0 0 >3 102 11 9 9 9 8 5 3 >1 100 11 12 10 19 0 0 7 > > > >Userid trans_id sales_code jan_sales jan_return >1 100 10 50 10 >2 101 10 10 5 >4 105 12 5 2 > > >Userid trans_id sales_code Jan_Sales Dec_sales >Nov_sales sept_sales >Jan_Returns dec_returns nov_returns sept_returns > >1 100 10 50 5 4 5 10 0 2 >1 >2 101 10 10 1 1 1 5 0 0 >0 >3 102 11 0 9 9 9 0 8 5 >3 >4 105 12 5 0 0 0 2 0 0 >0