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
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;
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;
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.
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;
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
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.
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.
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
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.