DATA Step, Macro, Functions and more

Back-fill empty rows with zero values

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Back-fill empty rows with zero values

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

L

 

 

 


Accepted Solutions
Solution
‎05-31-2017 07:26 PM
Super User
Posts: 10,483

Re: Back-fill empty rows with zero values

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;

View solution in original post


All Replies
Solution
‎05-31-2017 07:26 PM
Super User
Posts: 10,483

Re: Back-fill empty rows with zero values

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;
Super User
Posts: 5,079

Re: Back-fill empty rows with zero values

[ Edited ]

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.

Super User
Posts: 17,781

Re: Back-fill empty rows with zero values

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;

 

 

Contributor
Posts: 37

Re: Back-fill empty rows with zero values

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

Super User
Posts: 17,781

Re: Back-fill empty rows with zero values

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. 

 

Super User
Posts: 17,781

Re: Back-fill empty rows with zero values

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. 

 

Contributor
Posts: 37

Re: Back-fill empty rows with zero values

thanks
Valued Guide
Posts: 765

Re: Back-fill empty rows with zero values

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

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



Valued Guide
Posts: 765

Re: Back-fill empty rows with zero values

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

 

Contributor
Posts: 37

Re: Back-fill empty rows with zero values

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.


Valued Guide
Posts: 765

Re: Back-fill empty rows with zero values

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;

 

Contributor
Posts: 37

Re: Back-fill empty rows with zero values

it works but warning still exist. I modified one of the solution above. Thanks
Valued Guide
Posts: 765

Re: Back-fill empty rows with zero values

[ Edited ]

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.

Contributor
Posts: 37

Re: Back-fill empty rows with zero values

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 211 views
  • 2 likes
  • 5 in conversation