Missing Variables

[Costasg]

Hello all,

I need your help in an issue I am facing. I have a file with the fund's holdings (B), another one with all the funds' codes (Funds) and the third one with all the holding periods (Date). My problem is that at the final stage some of the dates, which have become the variables, are missing. What should I do to keep all the dates instead of only keeping the ones that there exist holdings?

Below is my code:

proc sql;
create table f as
select a.*,b.*
from work.Date as a left join work.b as b
on a.date=b.date;
quit;

proc sort data=work.f nodupkey;by code date;run;
data a; set f; format date monyy7.; run;
proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;
proc sort data= a2; by code date;run;
proc transpose data=a2 out=a3(where=(code^=0.1));
by code;
id date;
var value;
run;
data work.a4;
set work.a3;
drop _NAME_;
run;

proc sql;
create table a5 as
select a.*,b.*
from work.funds as a left join work.a4 as b
on a.code=b.code;
quit;
proc freq data=a5;run;

data asigns;
set WORK.a5;
array vol(63) mar1995 --jun2010;
array si (62) $  si1-si62;
do i= 2 to 63;
if vol(i) > vol(i-1) then si(i-1)='+';
else if vol(i) <  vol(i-1) then si(i-1)='-';
else si(i-1)='&';
end;
RUN;

[PGStats]

What are you trying to do with these three tables? What is the expected result?

PG

[Costasg]

well actually the problem is that one variable (SEP2010) is in the wrong order, instead of last one is 8th. I am attaching the output.

[ballardw]

I suspect you may be getting unexpected results with your array statement: array vol(63) mar1995 --jun2010

processing things in an order you aren't expecting.

You might want to put this code in the middle of your array processing loop to see if that is happening:

do i = 2 to 63;

y= vname (vol(i));

put y=;

<other code>

end;

drop y;

look in the log for the sequence of the values of y.

[Costasg]

Well, it is unlikely to be the problem there. I realized that the problem lies earlier, before the A3 table when I transpose the data. I have highlighted the code. Any suggestions?

proc sql;
create table f as
select a.*,b.*
from work.Date as a left join work.b as b
on a.date=b.date;
quit;

proc sort data=work.f nodupkey;by date code;run;
data a; set f; format date monyy7.; run;
proc sql; create table a_ as select distinct date,0.1 as code from a; quit;

data a2; set a a_;run;
proc sort data= a2; by code date;run;
proc transpose data=a2 out=a3(where=(code^=0.1));
by code;
id date;
var value;
run;
data work.a4;
set work.a3;
drop _NAME_;
run;

proc sql;
create table a5 as
select a.*,b.*
from work.funds as a left join work.a4 as b
on a.code=b.code;
quit;
proc freq data=a5;run;

data asigns;
set WORK.a5;
array vol(63) mar1995 --sep2010;
array si (62) $  si1-si62;
do i= 2 to 63;
if vol(i) > vol(i-1) then si(i-1)='+';
else if vol(i) <  vol(i-1) then si(i-1)='-';
else si(i-1)='&';
end;
RUN;

[Ksharp · Accepted solution]

If I understood what you mean.

data b;
infile 'c:\temp\b.txt' firstobs=2 expandtabs;
input date : ddmmyy10. code value;
monyy=put(date,monyy7.);
format date ddmmyy10.;
run;
data funds;
infile 'c:\temp\funds.txt' firstobs=2 expandtabs;
input  code ;
run;
data date;
infile 'c:\temp\date.txt' firstobs=2 expandtabs;
input date : ddmmyy10.;
monyy=put(date,monyy7.);
format date ddmmyy10.;
run;
proc sql;
create table have as
select x.*,value
 from b right join
 (select * from (select distinct code from funds),(select distinct monyy from date)) as x
 on  b.code = x.code and b.monyy=x.monyy;
quit;
 
proc transpose data=have out=want(drop=_name_) ;
by code;
id monyy;
var value;
run;

Ksharp

[Costasg]

Many Thanks Ksharp