Date: Tue, 16 Dec 2008 17:25:56 -0600
Reply-To: Mary <mlhoward@avalon.net>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Mary <mlhoward@AVALON.NET>
Subject: Re: Compare and read values into master data table
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
reply-type=original
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;
----- 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
