Help using Base SAS procedures

calculating returns

Reply
Contributor
Posts: 51

calculating returns

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

Valued Guide
Posts: 858

Re: calculating returns

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

Contributor
Posts: 51

Re: calculating returns

Please find attached the input and output files.

The A-RI-main file is the input :

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86

and rita.csv is my out output

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86

Thank you very much for the help

Valued Guide
Posts: 858

Re: calculating returns

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:

Obscaldt 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.

Contributor
Posts: 51

Re: calculating returns

what steps?

Valued Guide
Posts: 858

Re: calculating returns

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.

Contributor
Posts: 51

Re: calculating returns

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:

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/1999158.78
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999149.65
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999154.21
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/1999159.69
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/1999159.69



output

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/1999158.78
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999149.65
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999154.21
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/1999159.69
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/1999159.69

Thanks

Nilo

Contributor
Posts: 51

Re: calculating returns

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

Super User
Posts: 17,963

Re: calculating returns

Post a proc contents output from your input data set:

proc contents data=MSCI;

run;

Contributor
Posts: 51

Re: calculating returns

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

I did it:

The SAS System

The CONTENTS Procedure

     
Data Set NameWORK.ARIObservations28769
Member TypeDATAVariables4
EngineV9Indexes0
Created07/31/2015 12:07:01Observation Length88
Last Modified07/31/2015 12:07:01Deleted Observations0
Protection CompressedNO
Data Set Type SortedYES
Label
Data RepresentationWINDOWS_64
Encodingwlatin1 Western (Windows)

   
Data Set Page Size65536
Number of Data Set Pages39
First Data Page1
Max Obs per Page743
Obs in First Data Page727
Number of Data Set Repairs0
ExtendObsCounterYES
FilenameC:\Users\esy\AppData\Local\Temp\SAS Temporary Files\_TD8272_ESY-THINK_\ari.sas7bdat
Release Created9.0401M1
Host CreatedX64_7PRO

        
4RINum8BEST12.BEST32.
3caldtNum8MMDDYY10.MMDDYY10.
1comnamChar64$64.$64.
2dscdChar6$6.$6.

   
Sortedbycaldt dscd
ValidatedYES
Character SetANSI

Super User
Posts: 10,552

Re: calculating returns

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.

Valued Guide
Posts: 858

Re: calculating returns

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.

Contributor
Posts: 51

Re: calculating returns

Dear Ballardw,

I am copy pasting from my Editor!Smiley Sad

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,

NilooSmiley Sad

Super User
Posts: 17,963

Re: calculating returns

add ret to your keep statement.

Contributor
Posts: 51

Re: calculating returns

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 nowSmiley Sadit 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???)

comnamdscdcaldtret
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/19990
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/19990.005828
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999-0.00579
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999-0.02889
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/19990.011937
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999-0.0118
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999-0.02381
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/19990.02439
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/19990
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/19990.011937
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999-0.00593
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/19990.023669
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/19990.011593
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/19990
LIXIL GROUP - TOT RETURN IND70226312/31/1999-0.49033
NIPPON TELG. & TEL. - TOT RETURN IND74084712/31/19990.962035
LIXIL GROUP - TOT RETURN IND7022631/3/2000-0.49033

Thanks,

Niloo

Ask a Question
Discussion stats
  • 16 replies
  • 550 views
  • 3 likes
  • 5 in conversation