Date: Sat, 4 Dec 2010 07:07:04 -0800
Reply-To: Tanmoy Mukherjee <tkmcornell@yahoo.com>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Tanmoy Mukherjee <tkmcornell@YAHOO.COM>
Subject: Re: Iterative analysis
Content-Type: text/plain; charset=utf-8
Art,
Thanks for the link. I tried using one of the things mentioned here and also in some of the SUGI papers ( http://www2.sas.com/proceedings/sugi27/p011-27.pdf) called merging using the SET and the KEY option.
DATA SET_SET_KEY;
SET SUGI27.BAD_DEBTOR;
SET SUGI27.COMPANY_INDEXED
KEY = VAT_NUMBER;
RUN;
The reason for using this is because I have a large dataset to look up from and I want to look up selective columns alone. However, when I use the code with the KEY option it tells me that the KEY option is unrecognised.
The code I am using is as follows:
data mcsimulation (keep= Numeric_Loan_id Dist_Date Cur_cltv);
retain Dist_Date Numeric_Loan_id Cur_cltv;
set mylib.Poaloansample;
if OTS_Delinq_Code NE 'C' then delete;
Dist_Date=input(put(Distrib_Date,z8.),yymmdd8.);
format Dist_Date mmddyy10.;
if Numeric_Loan_ID NE 757176 then delete;
run;
/* one line of data which has the variables NUMERIC_LOAN_ID, DIST_DATE, CUR_CLTV*/
/*The DIST_DATE equals 10/1/2010 and in the next line of query I use a do loop to advance it by one month for the next 12 months*/
data fcstsheet;
Dist_Date_fcst=Dist_Date;
format Dist_Date_fcst Date mmddyy10.;
run;
/* This is the lookup table with only two variables DIST_DATE_FCST and HPA*/
datamcsimulation1 (keep=Numeric_Loan_ID Dist_Date_fcst cur_cltv cur_cltv_fcst HPA);retainDist_Date_fcst Numeric_Loan_ID cur_cltv cur_cltv_fcst HPA;setmcsimulation;cur_cltv_fcst=cur_cltv;do
Dist_Date_fcst=intnx(
/*This is where I am trying to look up and return the value HPA for the corresponding match on DIST_DATE_FCST*/
cur_cltv_fcst=cur_cltv_fcst*(1+HPA);output;end;run
I am getting the following error
ERROR: No key variables have been defined for file FCSTSHEET.
However if I omit the KEY Option then it runs fine but since the Lookup table does not have an entry for 10/1/2010 therefore it doesnt return the value and omits it from the resultant table i.e. MCSIMULATION1.;i = 0to12;'month',Dist_Date,i); formatDist_Date_fcst mmddyy10.; setfcstsheet key=Dist_Date_fcst;
I will appreciate if you can help me with the same.
Thanks and Regards,
Tanmoy
Tanmoy Kumar Mukherjee
3 Perrine Court,
East Brunswick, NJ 08816
Phone: 9173994540
Email: tkmcornell@yahoo.com
From: "art297@rogers.com" <art297@rogers.com>
To: SAS-L@LISTSERV.UGA.EDU; tkmcornell@yahoo.com
Cc:
Sent: Friday, December 3, 2010 11:33:12 PM
Subject: Re: Iterative analysis
Tanmoy,
Take a look at: http://www.google.ca/url?q=http://excelicious.wordpress.com/2009/09/12/lookup-tables-in-sas/&sa=U&ei=GsP5TMncE8K88gb28syrCw&ved=0CCEQFjAH&sig2=0431gJv6MGs7zgTHG7dTCg&usg=AFQjCNHr8ItWQ0egVnBorfO3-6N-7Le-4g
or, in short form, http://xrl.us/bh98bk
One of the things suggested in that article is to create a format from the one dataset and then apply it, with put statements, to the other dataset.
I think that you will find it to be very much like vlookup, only easier to accomplish and more generalizable.
Art
--- On Sat, 12/4/10, Tanmoy <tkmcornell@yahoo.com> wrote:
>From: Tanmoy <tkmcornell@yahoo.com>
>Subject: Re: Iterative analysis
>To: "Arthur Tabachneck" <art297@ROGERS.COM>, SAS-L@LISTSERV.UGA.EDU
>Date: Saturday, December 4, 2010, 1:17 AM
>
>
>Art thanks for your help but this solutions doesn't help my cause.
>
>All I am trying to do is a very simple matching. I am doing a simulation where at each step I am moving the month by 1 month. Once I have done that I want to read the HPA from the lookup file for that corresponding date.
>
>It is equivalent of the vlookup function in excel I.e.
>
>Vlookup(date, array, 2, False) where the HPA is in the second colun of the array with the first column being date.
>
>I am unable to find this matching solution anywhere.
>
>Sent via BlackBerry by AT&T
>
>-----Original Message-----
>From: Arthur Tabachneck <art297@ROGERS.COM>
>Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
>Date: Fri, 3 Dec 2010 19:26:10
>To: <SAS-L@LISTSERV.UGA.EDU>
>Reply-To: Arthur Tabachneck <art297@ROGERS.COM>
>Subject: Re: Iterative analysis
>
>Tanmoy,
>
>I haven't kept up with this entire thread, but does the following
>approximate what you are trying to do?
>
>data output;
> retain LOAN_NID CUR_LTV;
> format CUR_LTV 5.2;
> do until (eof1);
> set input end=eof1;
> output;
> end;
> do until (eof2);
> set forecast end=eof2;
> CUR_LTV=CUR_LTV* (1+hpa);
> output;
> end;
>run;
>
>HTH,
>Art
>--------
>On Fri, 3 Dec 2010 14:33:44 -0800, Tanmoy Mukherjee <tkmcornell@YAHOO.COM>
>wrote:
>
>>No the forecast file at this stage does not have geography at this stage;
>something I dont want to introduce right now
>So how would the code look like if I were to merge the two datasets.
>
>I will appreciate if you can let me know.
>
>
>
>Tanmoy Kumar Mukherjee
>3 Perrine Court,
>East Brunswick, NJ 08816
>Phone: 9173994540
>Email: tkmcornell@yahoo.com
>
>
>
>From: Rushi Patel <rushi.b.patel@GMAIL.COM>
>To: SAS-L@LISTSERV.UGA.EDU
>Cc:
>Sent: Friday, December 3, 2010 5:23:28 PM
>Subject: Re: Iterative analysis
>
>Does the forecast file have any geography like zip, state or cnty?
>
>When we apply hpa to mortgages it's usually by geo which is available
>in both datasets and its a simple merge.
>
>If the data sets are as you describe, u can convert your date in the
>input data to year, months and use the year and month values in your
>do loop. For ex, march would be 3. Span you do loop from mon (3 for
>march, 4 for apr) to the cycles of interest. It might get clumsy when
>year changes for a particular date, but is code able..
>
>Rushi
>
>On Friday, December 3, 2010, Tanmoy Mukherjee <tkmcornell@yahoo.com> wrote:
>> Dear All,
>>
>> I will sincerely appreciate if you can help me with the same. I have two
>files - "input" and "forecast". I am running a interative procedure to
>advance the date in the "input" file read the correpsonding HPA from the
>"froecast file and then compute the cur_ltv_fcst value for that date.
>>
>> So I have the query as follows :
>>
>> Data new;
>> Set input;
>> Do i=0 to 3;
>> DATE=intnx('month',DATE,i);
>> format DATE mmddyy10.;
>> output;
>> end;
>> run;
>>
>> I want to write a line of code that reads from the “forecast” file returns
>the right HPA for the date and then compute cur_ltv_fcst=cur_ltv*(1+hpa)
>>
>>
>> So the data set has the following format :
>>
>> Dataset forecast
>> DATE HPA
>> 10/1/2010 0.01
>> 11/1/2010 0.02
>> 12/1/2010 0.03
>>
>>
>> Dataset input
>> LOAN_NID DATE CUR_LTV
>> 1234 9/1/2010 80.00
>>
>>
>>
>>
>>
>> The output I need are :
>>
>> Dataset output
>> LOAN_NID DATE CUR_LTV
>> 1234 9/1/2010 80.00
>> 1234 10/1/2010 80.00 *
>(1+0.01) = 80.08
>> 1234 11/1/2010
>80.08*(1+0.02) = 82.42
>> 1234 12/1/2010
>82.42*(1+0.03) = 84.89
>>
>>
>> I will appreciate if you can let me know.
>>
>> Thanks and Regards,
>> Tanmoy
>>
>>
>>
>>
>
| 
