Consolidate the contents of three variables

Alexxxxxxx — Tue, 15 Sep 2020 22:27:05 GMT

Hi,

I am trying to transfer table 1 to table 2.

Table 1 (see attachment)

Rating

Watch

Outlook

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

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.

Re: Consolidate the contents of three variables

pink_poodle — Wed, 16 Sep 2020 15:38:50 GMT

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

topic Re: Consolidate the contents of three variables in SAS Programming

Consolidate the contents of three variables

Re: Consolidate the contents of three variables