Help using Base SAS procedures

Missing Variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Missing Variables

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;

Attachment
Attachment
Attachment

Accepted Solutions
Solution
‎05-07-2012 12:53 AM
Super User
Posts: 9,691

Re: Missing Variables

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,661

Re: Missing Variables

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

PG

PG
Frequent Contributor
Posts: 75

Re: Missing Variables

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.

Attachment
Super User
Posts: 10,550

Re: Missing Variables

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.

Frequent Contributor
Posts: 75

Re: Missing Variables

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;

Solution
‎05-07-2012 12:53 AM
Super User
Posts: 9,691

Re: Missing Variables

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

Frequent Contributor
Posts: 75

Re: Missing Variables

Many Thanks Ksharp

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 266 views
  • 3 likes
  • 4 in conversation