topic Re: Back-fill empty rows with zero values in SAS Programming

Back-fill empty rows with zero values

CheerfulChu — Wed, 31 May 2017 20:48:16 GMT

Dear Experts,

I have a problem back-filling empty rows or rows with no values on the column, named "Returns". Here are the dataset

Inputs:

Dataset A:

Ticker Date Returns

Appl 200209 0.2

Appl 200210 0.1

Appl 200211 0.21

Appl 200212 0.22

BAC 200209 0.1

BAC 200210 .

BAC 200212 0.23

C 200209 0.22

C 200210 0.11

Desired Outputs

Date Appl BAC C

200209 0.2 0.1 0.22

200210 0.1 0 0.11

200211 0.21 0.23 0

200212 0.22 0 0

My code:

%* Extract the full date series;

proc sort data=A (keep= yyyymm) nodupkey out=A1
by yyyymm;

%let xtemplist = Appl BAC C;

%do k = 1 %to 3;
%let ticker_name = %scan(&xtemplist,&k);

%* transpose the dataset;
data A;
set A;
if ticker = "&ticker_name";
&ticker_name = returns;
keep yyyymm &ticker_name;
run;

data A1;
merge A1(in=a) A (in=b);
by yyyymm;
if a and b;
run;

%end;

But the output A1:

yyyymm Appl BAC C

200209 0.2 0.1 0.22

200210 0.1 . 0.11

Do you have any ideas how to get the desired output?

Thank you for your help

Re: Back-fill empty rows with zero values

ballardw — Wed, 31 May 2017 21:25:23 GMT

If you ALWAYS want to replace EVERY missing numeric with 0, change the last data step to something like this. Note if you use i as a variable in any of your other datasets then come up with a different loop counter.

data A1;
   merge A1(in=a) A (in=b);
   by yyyymm;
   array _xxx_ _numeric_;
   do i=1 to dim( _xxx_);
      if missing(_xxx_[i]) then _xxx_[i]=0;
   end;
   drop i;
   if a and b; 
 run;

Re: Back-fill empty rows with zero values

Astounding — Wed, 31 May 2017 21:29:33 GMT

Note if you have other variables that you don't want to change, you have an easy tweak:

array _xxx_ {*} &XTEMPLIST;

Also, this statement is deleting mismatches:

if a and b;

It looks like removing it will restore the missing rows.

Re: Back-fill empty rows with zero values

Reeza — Wed, 31 May 2017 21:42:05 GMT

What you're doing is called a TRANSPOSE, try PROC TRANSPOSE.

Use PROC TRANSPOSE and then fill the missing values. You don't need macro's here, it only confuses things.

data have;
    informat ticker $8. date yymmn6.;
    input Ticker $ Date Returns;
    cards;
Appl   200209  0.2
Appl   200210  0.1
Appl   200211  0.21
Appl   200212  0.22
BAC   200209  0.1
BAC   200210  .
BAC   200212  0.23
C   200209  0.22
C   200210  0.11
;
run;

proc sort data=have nodupkey;
    by date ticker;
run;

proc transpose data=have out=flipped prefix=TICKER_;
    by date;
    id ticker;
    var returns;
    idlabel ticker;
run;

data final;
    set flipped;
    array _ticker(*) ticker_:;

    do i=1 to dim(_ticker);

        if _ticker(I)=. then
            _ticker(i)=0;
    end;
    drop i _name_;
    format date yymon7.;
run;

proc print data=final label noobs;
run;

Re: Back-fill empty rows with zero values

CheerfulChu — Wed, 31 May 2017 21:54:28 GMT

Your answer is good. I got all the 4 rows. The earlier answer can only give me the first two rows.

But I have a problem, I cant have prefix. ie ticker_appl , ticker_bac

How to fill up the empty values if there are no label such as a prefix, ticker_?

Thanks

Re: Back-fill empty rows with zero values

Reeza — Wed, 31 May 2017 21:58:19 GMT

You need the variable names to manually list them in the array. This is the best way to keep it dynamic, ie you don't need to know anything about the data to have it work. The labels usually are enough to avoid this issue but if you really insist my suggestion would be to rename the variables after replacing the name with the label.

There are are examples of how to do this dynamically on here if you search.

If you have the list of variables which you did from your code above you can use that instead in the array.

Re: Back-fill empty rows with zero values

Reeza — Wed, 31 May 2017 21:58:20 GMT

There are are examples of how to do this dynamically on here if you search.

If you have the list of variables which you did from your code above you can use that instead in the array.

Re: Back-fill empty rows with zero values

MikeZdeb — Wed, 31 May 2017 22:09:50 GMT

Hi, another idea ...

data a;
input ticker :$4. date :yymmn. returns @@;
format date yymmn6.;
datalines;
Appl 200209 0.2 Appl 200210 0.1 Appl 200211 0.21 Appl 200212 0.22
BAC 200209 0.1 BAC 200210 . BAC 200212 0.23 C 200209 0.22
C 200210 0.11
;

proc sort data=a;
by date ticker;
run;

proc transpose data=a out=b (drop=_name_);
by date;
id ticker;
var returns;
run;

proc stdize data=b out=b reponly missing=0;
run;

DATA SET: b

date Appl BAC C

200209 0.20 0.10 0.22
200210 0.10 0.00 0.11
200211 0.21 0.00 0.00
200212 0.22 0.23 0.00

Re: Back-fill empty rows with zero values

MikeZdeb — Wed, 31 May 2017 22:13:56 GMT

Could always use the following instead of the IF/THEN route ...

_ticker(i) = sum (_ticker(i), 0);

Re: Back-fill empty rows with zero values

CheerfulChu — Wed, 31 May 2017 22:50:03 GMT

your answer is neat. But I have this warning messages.

NOTE: No VAR statement is given. All numerical variables not named elsewhere
make up the first set of variables.
WARNING: At least one of the scale and location estimators of variable XXX
can not be computed. Variable XXX will not be standardized.

Re: Back-fill empty rows with zero values

MikeZdeb — Wed, 31 May 2017 23:07:07 GMT

Hi, works just fine even with warnings. If you want to get rid of them ...

proc stdize data=b out=b reponly missing=0;
var appl bac c;
run;

Re: Back-fill empty rows with zero values

CheerfulChu — Wed, 31 May 2017 23:26:00 GMT

it works but warning still exist. I modified one of the solution above. Thanks

Re: Back-fill empty rows with zero values

CheerfulChu — Wed, 31 May 2017 23:27:12 GMT

thanks

Re: Back-fill empty rows with zero values

MikeZdeb — Thu, 01 Jun 2017 00:02:08 GMT

Hi. I get no warnings if I specify a variable list in a VAR statement. You can also use ...

proc stdize data=b out=b reponly missing=0;
var _numeric_;
run;

LOG ...

46
47 proc stdize data=b out=b reponly missing=0;
48 var _numeric_;
49 run;

NOTE: There were 4 observations read from the data set WORK.B.
NOTE: The data set WORK.B has 4 observations and 4 variables.
NOTE: PROCEDURE STDIZE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Just curious, could you post what you use in PROC STDIZE and the LOG with the WARNINGS. You should be able to use it and use a lot less code than a data step with an array. and loop if all you want is to replace missing numerics with zeroes. Thanks.

Re: Back-fill empty rows with zero values

CheerfulChu — Thu, 01 Jun 2017 14:39:33 GMT

I agreed with you that your solution is very neat but I keep having the warning messages. My system will ping me for warning messages. Hence I cant have warning messages although they are harmless.

139? proc stdize data=B out=B reponly missing=0;
140? var _numeric_;
141? run;

WARNING: At least one of the scale and location estimators of variable F
can not be computed. Variable F will not be standardized.
WARNING: At least one of the scale and location estimators of variable T
can not be computed. Variable T will not be standardized.
WARNING: At least one of the scale and location estimators of variable U
can not be computed. Variable U will not be standardized.
NOTE: There were 4 observations read from the data set WORK.B.
NOTE: The data set WORK.B has 4 observations and 14 variables.
NOTE: PROCEDURE STDIZE used (Total process time):
real time 8.89 seconds
cpu time 0.01 seconds

If I re-run the above code, I will not have the messages but always appear on the first run. I not sure why.