BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CheerfulChu
Obsidian | Level 7

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

14 REPLIES 14
ballardw
Super User

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;
Astounding
PROC Star

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.

Reeza
Super User

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;

 

 

CheerfulChu
Obsidian | Level 7

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

Reeza
Super User

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. 

 

Reeza
Super User

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. 

 

CheerfulChu
Obsidian | Level 7
thanks
MikeZdeb
Rhodochrosite | Level 12
Could always use the following instead of the IF/THEN route ...

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



MikeZdeb
Rhodochrosite | Level 12

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

 

CheerfulChu
Obsidian | Level 7
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.


MikeZdeb
Rhodochrosite | Level 12

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;

 

CheerfulChu
Obsidian | Level 7
it works but warning still exist. I modified one of the solution above. Thanks
MikeZdeb
Rhodochrosite | Level 12

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.

CheerfulChu
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 15709 views
  • 4 likes
  • 5 in conversation