calculating returns

[niloo]

Hi,

I am new to sas and I am trying to calculate the return of some companies using this command: ret=dif(RI)/lag(RI);

I followed these steps to import my csv table to sas. I have 4 variables: 1)company name:comnam  2)Datastream code: dscd (this code is a mix of numbers and letters) 3) date :caldt        4) Return Index: RI

proc import out=ARI

datafile= "c:\evantus\A-RI-main.csv" dbms = csv replace;

getnames=yes;

datarow= 2;

GUESSINGROWS=20000;

format caldt date9.;

run;

proc sort data=ari;

by  caldt dscd;

run;

data reta;

set ari;

by caldt;

ret=dif(RI)/lag(RI);

if first. dscd then ret=.;

keep comnam dscd caldt ri;

run;

I have imported the data but when I run the last part of these statements the result does not show the "ret".

I used the same formula to calculate the return of mu index yesterday and it worked. The only difference between my Index file and my company file is that my index file only had 2 variables: caldt and RI.

What I used for my index was:

data MSCI;

set index;

by caldt;

ewretd=dif(RI)/lag(RI);

keep caldt ewretd;

run;

Could you please help me solve this issue?

Thanks,

Niloo

[Steelers_In_DC]

Give an example of your input and what output you are getting.

[niloo]

Please find attached the input and output files.

The A-RI-main file is the input :

comnam	dscd	caldt	RI
NIPPON TELG. & TEL. - TOT RETURN IND	740847	########	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	########	157.86

and rita.csv is my out output

comnam	dscd	caldt	RI
NIPPON TELG. & TEL. - TOT RETURN IND	740847	########	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	########	157.86

Thank you very much for the help

[Steelers_In_DC]

Based on your input and code it should work.  I feel you are leaving out some steps.

Input:

data index;

infile cards;

input caldt$ ri;

cards;

######## 157.86

######## 157.86

;

data MSCI;

set index;

by caldt;

ewretd=dif(RI)/lag(RI);

keep caldt ewretd;

run;

output:

Obs

caldt

ewretd

1

########

.

2

########

0

Your output doesn't look accurate, you can't have anything but caldt and ewretd in your output because of the keep statement.

[niloo]

what steps?

[Steelers_In_DC]

I don't know what you aren't showing me.  What I'm saying is with the statement "keep caldt ewretd;" you wouldn't get the output you are showing me.  You would get the output I pasted.  You can't have any other variable except caldt and ewretd with that keep statement.

[niloo]

sorry the previous input and outputs were for my index.

I tried but I could not attach the files so I copy pasted the first few lines:

input:

comnam	dscd	caldt	RI
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/10/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/13/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/14/1999	158.78
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/15/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/16/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/17/1999	155.13
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/20/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/21/1999	149.65
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/22/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/23/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/24/1999	155.13
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/27/1999	154.21
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/28/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/29/1999	159.69
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/30/1999	159.69

output

comnam	dscd	caldt	RI
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/10/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/13/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/14/1999	158.78
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/15/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/16/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/17/1999	155.13
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/20/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/21/1999	149.65
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/22/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/23/1999	153.3
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/24/1999	155.13
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/27/1999	154.21
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/28/1999	157.86
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/29/1999	159.69
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/30/1999	159.69

Thanks

Nilo

[niloo]

As you see the input and out put are exactly the same. the return is not calculated

[Reeza]

Post a proc contents output from your input data set:

proc contents data=MSCI;

run;

[niloo]

By input do you mean the ari dataset that I entered to sas?

I did it:

The SAS System

The CONTENTS Procedure

     

Data Set Name	WORK.ARI	Observations	28769
Member Type	DATA	Variables	4
Engine	V9	Indexes	0
Created	07/31/2015 12:07:01	Observation Length	88
Last Modified	07/31/2015 12:07:01	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	YES
Label
Data Representation	WINDOWS_64
Encoding	wlatin1 Western (Windows)

   

Data Set Page Size	65536
Number of Data Set Pages	39
First Data Page	1
Max Obs per Page	743
Obs in First Data Page	727
Number of Data Set Repairs	0
ExtendObsCounter	YES
Filename	C:\Users\esy\AppData\Local\Temp\SAS Temporary Files\_TD8272_ESY-THINK_\ari.sas7bdat
Release Created	9.0401M1
Host Created	X64_7PRO

        

4	RI	Num	8	BEST12.	BEST32.
3	caldt	Num	8	MMDDYY10.	MMDDYY10.
1	comnam	Char	64	$64.	$64.
2	dscd	Char	6	$6.	$6.

   

Sortedby	caldt dscd
Validated	YES
Character Set	ANSI

[ballardw]

Can't be the "output".

This code:

data reta;

set ari;

by caldt;

ret=dif(RI)/lag(RI);

if first. dscd then ret=.;

keep comnam dscd caldt ri;

run;

will not execute for two reasons: 1) you cannot use First. notation with a variable that doesn't appear on a BY statement 2) there is a space between First. and the variable DSCD. You either ran different code or did not show the code you actually ran.

[Steelers_In_DC]

Ballardw;

There's another piece of code that has a keep statement that only has 2 variables, the output has 4, so that also cannot be the code that goes along with that output.

[niloo]

Dear Ballardw,

I am copy pasting from my Editor!

proc import out=ARI

datafile= "c:\evantus\A-RI-main.csv" dbms = csv replace;

getnames=yes;

datarow= 2;

GUESSINGROWS=20000;

format caldt date9.;

run;

proc sort data=ari;

by  caldt dscd;

run;

data reta;

set ari;

by caldt dscd;

ret=dif(RI)/lag(RI);

if first.dscd then ret=.;

keep comnam dscd caldt ri;

run;

Even after deleting the space between  first. and dscd it does not work.

DSCD is on the by statement.

Thanks,

Niloo

[Reeza]

add ret to your keep statement.

[niloo]

Hi Reeza,

Thank you so much for your answer. I added ret and deleted RI from the keep statement. a column called ret was added to sas results. however, all the values of the column were "."

So I deleted the if statement: if first.dscd then ret=.;

After this I got results using only the following the statements:

data reta;

set ari;

by caldt dscd;

ret=dif(RI)/lag(RI);

keep comnam dscd caldt ret;

run;

I am not sure if my results are correct or not now that I deleted the if statement. Can you tell me what is wrong with my if statement?

This is how the result looks like now:(it looks perfect but some of the rets in later rows are too big and I guess maybe it is because of deleting the if statement???)

comnam	dscd	caldt	ret
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/10/1999
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/13/1999	0
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/14/1999	0.005828
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/15/1999	-0.00579
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/16/1999	-0.02889
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/17/1999	0.011937
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/20/1999	-0.0118
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/21/1999	-0.02381
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/22/1999	0.02439
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/23/1999	0
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/24/1999	0.011937
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/27/1999	-0.00593
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/28/1999	0.023669
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/29/1999	0.011593
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/30/1999	0
LIXIL GROUP - TOT RETURN IND	702263	12/31/1999	-0.49033
NIPPON TELG. & TEL. - TOT RETURN IND	740847	12/31/1999	0.962035
LIXIL GROUP - TOT RETURN IND	702263	1/3/2000	-0.49033

Thanks,

Niloo