Hi, I am trying to transfer table 1 to table 2. Table 1 (see attachment) ID Rating Watch Outlook 1 Rating: CCC+ Rating Range: Mar-20-2020 to Present Rating: B Rating Range: Jan-09-2019 to Mar-20-2020 CreditWatch: Watch Neg CreditWatch Range: Mar-10-2020 to Mar-20-2020 CreditWatch: Watch Neg CreditWatch Range: Aug-29-2019 to Oct-08-2019 Outlook: Negative Outlook Range: Mar-20-2020 to Present Outlook: Negative Outlook Range: Oct-08-2019 to Mar-10-2020 Outlook: Negative Outlook Range: Jan-10-2019 to Aug-29-2019 2 Rating: BBB+ Rating Range: Jan-07-2019 to Present CreditWatch: Watch Neg CreditWatch Range: Apr-03-2020 to Present Outlook: Stable Outlook Range: Apr-03-2020 to Present Table 2 Obs ID date rating outlookorwatch Note 1 1 1/09/2019 B stable outlook 2 1 1/10/2019 B negative outlook 3 1 8/29/2019 B negative watch 4 1 10/08/2019 B negative outlook 5 1 3/10/2020 B negative watch 6 1 3/20/2020 CCC+ negative outlook 7 2 1/7/2019 BBB+ stable outlook 8 2 4/3/2020 BBB+ 1 There are three variables, each of them contains at least one recode. For each record, it contains a level name (i.e., Rating, CreditWatch or Outlook) and a time range (i.e., Rating range, CreditWatch Range or Outlook range). Step1, 1) Find the earliest date of these record. In this sample, the earliest record is stored in the ‘Rating’ variable, ‘Rating: B Rating Range: Jan-09-2019 to Mar-20-2020’ So, the value in ‘Rating’ converts to ‘rating’ variable in Table 2 as B. The value in ‘Rating Range’ converts to ‘Date’ variable in Table 2 as 1/09/2019. 2) At the date Jan-09-2019, there is no record of ‘CreditWatch’ or ‘Outlook’. So, the value in ‘outlookorwatch’ is ‘stable outlook’. 3)The value in ‘Rating’ variable does not change until the next record in ‘rating’ variable happens. Step2, 1) Find the second earliest date of these record. It is the last record in ‘Outlook’ variable ‘Outlook: Negative Outlook Range: Jan-10-2019 to Aug-29-2019’ So, the value in ‘Outlook’ converts to ‘outlookorwatch’ variable in Table 2 as ‘negative outlook’. the value in ‘Outlook Range’ converts to ‘Date’ variable in Table 2 as 1/10/2019. Step3, 1) find the third earliest date of these record. It is stored in the ‘Watch’ variable as ‘CreditWatch: Watch Neg CreditWatch Range: Aug-29-2019 to Oct-08-2019’ So, the value in ‘Watch’ converts to ‘outlookorwatch’ variable in Table 2 as ‘negative watch’. the value in ‘Outlook Range’ converts to ‘Date’ variable in Table 2 as 8/29/2019. Step4, if ‘CreditWatch’ record and ‘Outlook’ happen at same time, I would like to set Note as 1 and does not file any value in ‘outlookorwatch’ variable. By using ID 2 in table 1 as a sample, Record in variable ‘Watch’ and ‘Outlook’ happens at the same date (i.e., Apr-03-2020) then the value in ‘outlookorwatch’ variable is empty and Note is 1. I have the code to separate the record in each cell, but I have no idea to put them together (i.e., step1-step4). /*matching datastream_ID with WRDS and S&P20200903*/
/*DS_SP_Step2 create S&P_ID,CUSIP,ISIN,CIK,Ticker and name standardization*/
proc import datafile = "C:\Users\70660\Desktop\question20200915.xlsx"
OUT=sheet1 DBMS=XLSX replace;
Sheet="sheet1";
RANGE="A1:D3";
GETNAMES=YES;
RUN;
data sheet2;
length
rating1 $50.
rating_range1 $200.
;
set sheet1;
rating=tranwrd(rating,'0D0A'x, ',');
rating=tranwrd(rating,',,', ',');
count=countc(rating,",")+1;
do i=1 to count by 2;
rating1=strip(tranwrd(scan(rating,i,','),'Rating:',''));
rating_range1=strip(tranwrd(scan(rating,i+1,','),'Rating Range:',''));
output;
end;
run;
data sheet3(drop=i count);
Length
Credit1 $50.
Credit_Range1 $200.
;
set sheet2;
Credit=tranwrd(Credit,'0D0A'x, ',');
Credit=tranwrd(Credit,',,', ',');
count=countc(Credit,",")+1;
do i=1 to count by 2;
Credit1=strip(tranwrd(scan(Credit,i,','),'CreditWatch: Watch',''));
Credit_Range1=strip(tranwrd(scan(Credit,i+1,','),'CreditWatch Range:',''));
output;
end;
run;
data SP_table4(drop=i count);
Length
Outlook1 $50.
Outlook_Range1 $200.
;
set sheet3;
Outlook=tranwrd(Outlook,'0D0A'x, ',');
Outlook=tranwrd(Outlook,',,', ',');
count=countc(Outlook,",")+1;
do i=1 to count by 2;
Outlook1=strip(tranwrd(scan(Outlook,i,','),'Outlook:',''));
Outlook_Range1=strip(tranwrd(scan(Outlook,i+1,','),'Outlook Range:',''));
output;
end;
run; Could you please give me some suggestions about this? Thanks in advance.
... View more