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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

6 REPLIES 6
PGStats
Opal | Level 21

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

PG

PG
Costasg
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

Many Thanks Ksharp

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 979 views
  • 3 likes
  • 4 in conversation