topic Re: Missing Variables in SAS Procedures

Missing Variables

Costasg — Fri, 04 May 2012 16:38:17 GMT

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;

Re: Missing Variables

PGStats — Fri, 04 May 2012 17:00:56 GMT

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

Re: Missing Variables

Costasg — Fri, 04 May 2012 17:14:15 GMT

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.

Re: Missing Variables

ballardw — Fri, 04 May 2012 17:55:40 GMT

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=;

end;

drop y;

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

Re: Missing Variables

Costasg — Fri, 04 May 2012 18:22:09 GMT

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;

Re: Missing Variables

Ksharp — Mon, 07 May 2012 04:53:55 GMT

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

Re: Missing Variables

Costasg — Mon, 07 May 2012 08:02:05 GMT

Many Thanks Ksharp