topic Re: How to winsorize and complete the lost data in SAS Programming

How to winsorize and complete the lost data

lixuan — Thu, 27 Jul 2017 21:09:42 GMT

Hi , I have a new question here. I have a database like this. For example, I want the variable yymm which is date format to be 4 quarters (one year) before the variable yymm_e, but you can see some data missed, so I wanna complete my lost data.

data have;
informat company $15. roe 4.3 yymm yymm_e yymmn6.;
format yymm yymm_e yymmn6.;
input company$  roe yymm  yymm_e ;
cards;
a 0.05 198503 198603
a 0.03 198512 198603
a 0.3  198606 198703
b 0.4  198812 198902
;
run;

The table structure I want should be as following . Thanks&Regards.

Re: How to winsorize and complete the lost data

Reeza — Thu, 27 Jul 2017 20:34:08 GMT

What is the logic here?

@lixuan wrote:

Hi , I have a new question here. I have a database like this. For example, I want the variable yymm which is date format to be 3 months before the variable yymm_e, but you can see some data missed, so I wanna complete my lost data.
data have;
informat company $15. roe 4.3 yymm yymm_e yymmn6.;
format yymm yymm_e yymmn6.;
input company$  roe yymm  yymm_e ;
cards;
a 0.05 198601 198603
a 0.03 198512 198603
a 0.3  198611 198703
b 0.4  198812 198902
;
run;
The table structure I want should be as following . Thanks&Regards.

Re: How to winsorize and complete the lost data

PeterClemmensen — Thu, 27 Jul 2017 20:54:48 GMT

What do you mean by complete lost data?

For Winsorization, check out this article

http://blogs.sas.com/content/iml/2017/02/08/winsorization-good-bad-and-ugly.html

Re: How to winsorize and complete the lost data

lixuan — Thu, 27 Jul 2017 21:07:21 GMT

Maybe my expression is not clear. I have revised my question. The data I got wasn't complete, some data were missing, so i want to make it up. Before I do this job, I need to know which data is missing. I am sorry because english is not my mother language. If any questions or suggestions Pls let me know. Thanks a lot.

Re: How to winsorize and complete the lost data

Reeza — Thu, 27 Jul 2017 21:22:32 GMT

PROC TIMESERIES can be used to fill in missing time periods, as long as you can clearly define them.

I'm not quite sure what your starting/ending interval are but it should work for you.

See a fully worked example here:

https://gist.github.com/statgeek/07a3708dee1225ceb9d4aa75daab2c52

Re: How to winsorize and complete the lost data

novinosrin — Thu, 27 Jul 2017 23:06:38 GMT

Hi, I concur with Reeza's suggestion as SAS ETS procs are pretty much built for such applications. However, if you want a datastep solution, I can give it a shot for you. Before that, can you please clarify the logic from 5th row in your wanted output:

I apologise if this is lame, but if you don't mind I would appreciate that so much.

Re: How to winsorize and complete the lost data

Shmuel — Fri, 28 Jul 2017 01:33:49 GMT

Create a table of all combinations having the company code, 4 previous year quarters and current year-month.

You need a list of the companies, can be created by sql selectint distinct company then do:

proc sql;
    create table companies as
    select distint company
    from have;
quit;

data help;
   set companies;
         length year $4  yymm $6;
         retain year='1988';  /* previous year */
         array qtr $ '03' '06' '09' '12';
         do i=1 to 4;
              yymm = year || qtr(i);
          output;
         keep company yymm;
run;

data want;
      merge help have;
     by company yymm;
run;

taht code assumed yymm is a charcter variable.

in case it is a date variable with a desired format, adapt the code to your needs.

Re: How to winsorize and complete the lost data

lixuan — Fri, 28 Jul 2017 13:34:14 GMT

Yes, I do want to create a table with full comany name and quarter date, then I can merge with original table to get the result. But you can see the dates are different. As for company a, the first two obervations of yymm_e are both 198603, so there are two yymm missed which are 198506 and 198509. The third observation a whose yymm_e is 198703, and only one observation, so there are three yymm missed which are 198603,198609 and 198612. That's what I want.

In summary, my final goal is to complete the missing data of roe instead of deleting them. Thanks a lot.

Re: How to winsorize and complete the lost data

Reeza — Fri, 28 Jul 2017 15:10:57 GMT

@lixuan wrote:

Yes, I do want to create a table with full comany name and quarter date, then I can merge with original table to get the result. But you can see the dates are different. As for company a, the first two obervations of yymm_e are both 198603, so there are two yymm missed which are 198506 and 198509. The third observation a whose yymm_e is 198703, and only one observation, so there are three yymm missed which are 198603,198609 and 198612. That's what I want.

In summary, my final goal is to complete the missing data of roe instead of deleting them. Thanks a lot.

How is the end date identified or known?

Re: How to winsorize and complete the lost data

lixuan — Fri, 28 Jul 2017 15:24:59 GMT

The end date is four quarters before yymm_e.

Re: How to winsorize and complete the lost data

Reeza — Fri, 28 Jul 2017 15:59:11 GMT

I tried and then realized I still don't understand your logic...so here's a start. I found the smallest and largest end and then used INTCK to determine the number of periods needed. Then looped through to create a quarter record for each of the starting points using INTNX.

Your last record, b, starts from a year before but that doesn't apply to the first record so I don't know why it's inconsistent. Anyways, build the skeleton table with all your periods and then merge it into your original dataset was the approach I was trying to take.

Documentation for INTCK/INTNX

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0g056g35ez8son1sfavozh0lfb3.htm#n1d3z2e1f8u8drn1j70702vf4q3h

data have;
	informat company $15. roe 4.3 yymm yymm_e yymmn6.;
	format yymm yymm_e yymmn6.;
	input company$  roe yymm yymm_e;
	cards;
a 0.05 198503 198603
a 0.03 198512 198603
a 0.3  198606 198703
b 0.4  198812 198902
;
run;

proc sql ;
	create table skeleton as select company, min(yymm) as start, max(yymm_e) as 
		end from have group by company;
quit;

data skeleton_all;
	set skeleton;
	num_periods=intck('qtr', start, end);

	do i=0 to num_periods;
		date=intnx('qtr', start, i, 'e');
		format date yymmn6.;
		output;
	end;
	format start end yymmn6.;
run;

Re: How to winsorize and complete the lost data

lixuan — Fri, 28 Jul 2017 17:17:36 GMT

Thanks, sorry, I didnt expalin my logic very clearly. The blank table I want like this . yymm is in the four quarters before the yymm_e, so the loop end should be 4. I tried but failed.

data want;
set have;
do i=1 to 4;
date=intnx('qtr',yymm_e,-i,'e');
format date yymmn6.;
end;
run;

Re: How to winsorize and complete the lost data

Reeza — Fri, 28 Jul 2017 17:26:43 GMT

The logic is still unclear. I think this is one of those times you really need to sit down and draw out the full logic. Or hopefully someone else will understand.

Re: How to winsorize and complete the lost data

lixuan — Fri, 28 Jul 2017 17:27:05 GMT

Maybe it will use array to finish it , and I am not familar with the usage of array.

Re: How to winsorize and complete the lost data

lixuan — Fri, 28 Jul 2017 17:28:27 GMT

OK, thank you very much.

Re: How to winsorize and complete the lost data

Reeza — Fri, 28 Jul 2017 17:31:25 GMT

From what you've shown here, arrays won't help.

Re: How to winsorize and complete the lost data

Shmuel — Fri, 28 Jul 2017 21:45:19 GMT

I see that some companies have only one year data while other may have more.

I understand that for each company-year you want the all 4 periods.

I suggest you try next code:

proc sql;
    create table companies as
    select distint company, yymm_e
    from have;
quit;

data skeleton;
   set companies;
         year = year(yymm_e) -1;  /* assuming yymm_e is a sas date variable */  
             /*   if it is a six digits number then year = int(yymm_e / 100) -1;     */
         array qtr  03 06 09 12;
         do i=1 to 4;
              yymm = mdy(qtr,1,year);
             output;
         end;
         format yymm yymmn6. ;
         keep company yymm;
run;

data want;
  merge skeleton have;
     by company yymm;
run;

Look at each of the tables that were created at the process.

If the want table is not the desired result - please post the result of one company and show what's wrong with it and what should be.

Re: How to winsorize and complete the lost data

Reeza — Fri, 28 Jul 2017 22:09:58 GMT

I believe this question has been answered here:

https://communities.sas.com/t5/Base-SAS-Programming/How-can-I-create-a-table/m-p/383905#M91605