Consolidate the contents of three variables

[Alexxxxxxx]

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.

[pink_poodle]

You would need to store them in arrays. @mkeintz, do you have any suggestions?